ORACLE PROJECTS: Electronic Invoice Generation for Vibac Spa

VIBAC SPA CUSTOMER

Step 1: Create Package Spec and Body to Load and Export

CREATE OR REPLACE PACKAGE APPS.LOKA_VIBAC_ELECTRONIC_INVOICES

PROCEDURE LNT_VIBAC_REEL_TABLE_INSERTION (

ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2,

P_ORDER_NUMBER IN VARCHAR2,

P_DELIVERY_ID IN VARCHAR2);

PROCEDURE LNT_VIBAC_PALLET_INSERTION (ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2,

P_ORDER_NUMBER IN VARCHAR2,

P_DELIVERY_ID IN VARCHAR2);

PROCEDURE LNT_VIBAC_REEL_EXPORT (ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2,

-- P_ORDER_NUMBER IN VARCHAR2,

P_DELIVERY_ID IN VARCHAR2);

PROCEDURE LNT_VIBAC_PALLET_EXPORT (ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2,

-- P_ORDER_NUMBER IN VARCHAR2,

P_DELIVERY_ID IN VARCHAR2);

END LOKA_VIBAC_ELECTRONIC_INVOICES;

CREATE OR REPLACE PACKAGE BODY APPS.loka_vibac_electronic_invoices

/*==============================================================================================

*===============================================================================================

* File :

* Package Name :LOKA_VIBAC_ELECTRONIC_INVOICES

* @author : LOKANADHAM THANDLAM

* @since : 01-Mar-2015

* Change History:

* Author Date Version Change (include Bug#IF appropriate)

*+-------------+---------------+----------+----------------------------------------------------+

* LOKANADHAM THANDLAM 01-Mar-2015 1.0 Initial Creation

*+-------------+---------------+----------+----------------------------------------------------+

* DESCRIPTION:

*+---------------------------------------------------------------------------------------------+*/

PROCEDURE lnt_vibac_reel_table_insertion (

ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2,

p_order_number IN VARCHAR2,

p_delivery_id IN VARCHAR2)

CURSOR loka_cur

SELECT DISTINCT

NULL production_number,

NULL vibac_order_number,

NULL vibac_order_line_number,

NULL former_order,

NULL film_type,

NULL film_user,

NULL guage,

NULL slitter_number,

NULL slitting_batch,

NULL blade_position,

NULL defect1,

NULL defect2,

NULL defect3,

NULL status,

NULL mother_reel_code,

NULL mother_reel_weight,

NULL mother_reel_length,

NULL mother_reel_height,

NULL mother_reel_length_product,

NULL reel_grade,

NULL extrusion_production,

NULL metal_production,

'O' daughter_reel_packaging,

'O' treatment,

NULL vibac_product_code,

wsv.delivery_detail_id,

wsv.delivery_line_id,

(SELECT DISTINCT

REPLACE (

REPLACE (excise_invoice_no, 'EX/', ' '),

'E',

' '),

'E/',

' '),

'/',

' ')

xx_excise_invoice_no

FROM jai_om_wsh_lines_all jowla,

jai_cmn_cus_addresses jcca

WHERE jowla.delivery_id IN (SELECT delivery_id

FROM wsh_new_deliveries

WHERE name = p_delivery_id)

AND jowla.customer_id = jcca.customer_id)

excise_invoice_no,

(SELECT DECODE (TO_CHAR (y.slitting_date, 'YYYY'),

'2014', '4',

'2015', '5',

'2016', '6')

xyear

FROM xxmax_slitting_output z, xxmax_slitting_headers y

WHERE y.doc_id = z.doc_id

AND z.roll_number = wsv.lot_number)

xyear,

wsv.delivery_id delivery_number,

party.party_name customer_name,

cust_acct.account_number customer_number,

SUBSTR (ship_su.location, 5) invoice_to_location,

ship_loc.address1 invoice_to_address1,

ship_loc.address2 invoice_to_address2,

ship_loc.address3 invoice_to_address3,

ship_loc.address4 invoice_to_address4,

ship_loc.address1 delivery_address,

ship_loc.city,

ship_loc.postal_code,

ship_loc.state,

ship_loc.province,

h.freight_carrier_code transporter,

wsv.freight_terms_code,

wsv.attribute11 lr_type,

wsv.attribute12 load,

jcio.ec_code,

jcio.cst_reg_no,

h.header_id,

h.order_number,

wsv.lot_number,

mtl.segment1 film,

mtl.segment2 width,

ROUND ( (mtl.segment2 * 4.6 / 1000)) core_weight,

mtl.segment2 width1,

REPLACE (REPLACE (mtl.description, '°', ' Deg. '),

'µ',

' Mic. ')

description,

wsv.preferred_grade grade_code,

wsv.attribute1 packing_number,

wsv.picked_quantity rq_1,

wsv.picked_quantity2 rq_2,

wsv.requested_quantity_uom rq_uom1,

wsv.requested_quantity_uom2 rq_uom2,

mtl.segment3 id,

mtl.segment4 od,

(SELECT attribute4

FROM mtl_lot_numbers

WHERE lot_number = wsv.lot_number

AND organization_id = '106')

LENGTH,

TO_CHAR (wsv.net_weight,

'9G999D0',

'NLS_NUMERIC_CHARACTERS = '',.''')

net_weight,

(SELECT no_of_joints

FROM xxmax_slitting_output

WHERE roll_number = wsv.lot_number)

no_of_joints,

(SELECT TO_CHAR (y.slitting_date, 'DDMMYYYY') production_date

FROM xxmax_slitting_output z, xxmax_slitting_headers y

WHERE y.doc_id = z.doc_id

AND z.roll_number = wsv.lot_number)

production_date,

(SELECT TO_CHAR (y.slitting_date, 'HHMMSS') production_date

FROM xxmax_slitting_output z, xxmax_slitting_headers y

WHERE y.doc_id = z.doc_id

AND z.roll_number = wsv.lot_number)

production_time,

jcio.customer_id,

h.attribute3 consignee_name,

h.attribute4 consignee_addr

FROM oe_order_headers_all h,

hz_cust_accounts cust_acct,

oe_order_lines_all ol,

hz_parties party,

hz_cust_site_uses_all ship_su,

hz_locations ship_loc,

hz_party_sites ship_ps,

wsh_deliverables_v wsv,

hz_cust_acct_sites_all ship_cas,

jai_cmn_cus_addresses jcio,

mtl_system_items mtl

WHERE h.order_number = p_order_number

AND wsv.delivery_id = p_delivery_id

AND h.sold_to_org_id = cust_acct.cust_account_id

AND cust_acct.party_id = party.party_id

AND h.header_id = ol.header_id

AND h.ship_to_org_id = ship_su.site_use_id

AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id

AND ship_cas.party_site_id = ship_ps.party_site_id

AND ship_ps.location_id = ship_loc.location_id

AND ol.ship_from_org_id = mtl.organization_id

AND ol.inventory_item_id = mtl.inventory_item_id

AND mtl.inventory_item_id = wsv.inventory_item_id

AND mtl.organization_id = wsv.organization_id

AND ol.line_id = wsv.source_line_id

AND h.header_id = wsv.source_header_id

AND wsv.customer_id = cust_acct.cust_account_id

AND jcio.customer_id = cust_acct.cust_account_id

AND jcio.address_id = ship_cas.cust_acct_site_id

AND jcio.address_id > 0

ORDER BY wsv.attribute1;

BEGIN

FOR i IN loka_cur

LOOP

-- Inserting data into staging table

INSERT

INTO apps.maxlnt_vibac_reel_table (vibac_reel_sequence,

vibac_pallet_sequence,

production_number,

vibac_order_number,

vibac_order_line_number,

former_order,

film_type,

film_user,

guage,

slitter_number,

slitting_batch,

blade_position,

defect1,

defect2,

defect3,

status,

mother_reel_code,

mother_reel_weight,

mother_reel_length,

mother_reel_height,

mother_reel_length_product,

reel_grade,

extrusion_production,

metal_production,

vibac_product_code,

daughter_reel_packaging,

treatment,

excise_invoice_no,

xyear,

delivery_number,

delivery_detail_id,

delivery_line_id,

customer_name,

customer_number,

invoice_to_location,

invoice_to_address1,

invoice_to_address2,

invoice_to_address3,

invoice_to_address4,

delivery_address,

city,

postal_code,

state,

province,

transporter,

freight_terms_code,

lr_type,

load,

ec_code,

cst_reg_no,

header_id,

order_number,

lot_number,

film,

width,

core_weight,

width1,

description,

grade_code,

packing_number,

rq_1,

rq_2,

rq_uom1,

rq_uom2,

id,

od,

LENGTH,

net_weight,

no_of_joints,

production_date,

production_time,

customer_id,

consignee_name,

consignee_addr)

VALUES (

'B' || 'X' || i.xyear

|| RPAD (

SUBSTR (

LPAD (

LTRIM (TO_CHAR (max_lnt_reel_seq_a.NEXTVAL),

' '),

'0'),

6),

' '),

'P' || 'X' || i.xyear

|| RPAD (

SUBSTR (

LPAD (

LTRIM (TO_CHAR (max_lnt_reel_seq_b.NEXTVAL),

' '),

'0'),

6),

' '),

i.production_number,

i.vibac_order_number,

i.vibac_order_line_number,

i.former_order,

i.film_type,

i.film_user,

i.guage,

i.slitter_number,

i.slitting_batch,

i.blade_position,

i.defect1,

i.defect2,

i.defect3,

i.status,

i.mother_reel_code,

i.mother_reel_weight,

i.mother_reel_length,

i.mother_reel_height,

i.mother_reel_length_product,

i.reel_grade,

i.extrusion_production,

i.metal_production,

i.vibac_product_code,

i.daughter_reel_packaging,

i.treatment,

trim( i.excise_invoice_no),

i.xyear,

i.delivery_number,

i.delivery_detail_id,

i.delivery_line_id,

NVL (i.customer_name, 'VIBAC SPA'),

i.customer_number,

i.invoice_to_location,

i.invoice_to_address1,

i.invoice_to_address2,

i.invoice_to_address3,

i.invoice_to_address4,

i.delivery_address,

i.city,

i.postal_code,

i.state,

i.province,

i.transporter,

i.freight_terms_code,

i.lr_type,

i.load,

i.ec_code,

i.cst_reg_no,

i.header_id,

i.order_number,

i.lot_number,

i.film,

i.width,

i.core_weight,

i.width1,

i.description,

i.grade_code,

i.packing_number,

i.rq_1,

i.rq_2,

i.rq_uom1,

i.rq_uom2,

i.id,

i.od,

i.LENGTH,

i.net_weight,

i.no_of_joints,

i.production_date,

i.production_time,

i.customer_id,

i.consignee_name,

i.consignee_addr);

COMMIT;

-- DBMS_OUTPUT.put_line (' SEQ:' || max_lnt_reel_seq_a.CURRVAL);--

END LOOP;

DBMS_OUTPUT.put_line (' Total Reocrd(s) inserted ' || SQL%ROWCOUNT);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (' Error in Below Package');

DBMS_OUTPUT.put_line (

'LOKA_VIBAC_ELECTRONIC_INVOICES.LNT_VIBAC_REEL_TABLE_INSERTION');

DBMS_OUTPUT.put_line (

' Error Information => ' || SQLCODE || SQLERRM);

END lnt_vibac_reel_table_insertion;

PROCEDURE lnt_vibac_pallet_insertion (ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2,

p_order_number IN VARCHAR2,

p_delivery_id IN VARCHAR2)

CURSOR lnt_shipping_out

SELECT DISTINCT

NULL production_number,

NULL vibac_order_number,

NULL vibac_order_line_number,

NULL former_order,

NULL film_type,

NULL film_user,

NULL guage,

NULL slitter_number,

NULL slitting_batch,

NULL blade_position,

NULL defect1,

NULL defect2,

NULL defect3,

NULL status,

NULL mother_reel_code,

NULL mother_reel_weight,

NULL mother_reel_length,

NULL mother_reel_height,

NULL mother_reel_length_product,

NULL reel_grade,

NULL extrusion_production,

NULL metal_production,

'O' daughter_reel_packaging,

'O' treatment,

NULL vibac_product_code,

'01' number_of_reels_per_pallet,

NULL pallet_base,

NULL pallet_height,

-- DECODE (TO_CHAR (SYSDATE, 'YYYY'),'2014', '4', '2015', '5', '2016', '6') XYEAR,

(SELECT DECODE (TO_CHAR (y.slitting_date, 'YYYY'),

'2014', '4',

'2015', '5',

'2016', '6')

xyear

FROM xxmax_slitting_output z, xxmax_slitting_headers y

WHERE y.doc_id = z.doc_id

AND z.roll_number = wsv.lot_number)

xyear,

wsv.delivery_id delivery_number,

party.party_name customer_name,

wsv.delivery_detail_id,

wsv.delivery_line_id,

(SELECT DISTINCT

REPLACE (

REPLACE (excise_invoice_no, 'EX/', ' '),

'E',

' '),

'E/',

' '),

'/',

' ')

xx_excise_invoice_no

FROM jai_om_wsh_lines_all jowla,

jai_cmn_cus_addresses jcca

WHERE jowla.delivery_id IN (SELECT delivery_id

FROM wsh_new_deliveries

WHERE name = p_delivery_id)

AND jowla.customer_id = jcca.customer_id)

excise_invoice_no,

cust_acct.account_number customer_number,

SUBSTR (ship_su.location, 5) invoice_to_location,

ship_loc.address1 invoice_to_address1,

ship_loc.address2 invoice_to_address2,

ship_loc.address3 invoice_to_address3,

ship_loc.address4 invoice_to_address4,

ship_loc.address1 delivery_address,

ship_loc.city,

ship_loc.postal_code,

ship_loc.state,

ship_loc.province,

h.freight_carrier_code transporter,

wsv.freight_terms_code,

wsv.attribute11 lr_type,

wsv.attribute12 load,

jcio.ec_code,

jcio.cst_reg_no,

h.header_id,

h.order_number,

wsv.lot_number,

mtl.segment1 film,

mtl.segment2 width,

(mtl.segment2 * 4.6 / 1000) core_weight,

mtl.segment2 width1,

-- mtl.segment1 film_type,

--mtl.description,

REPLACE (REPLACE (mtl.description, '°', ' Deg. '),

'µ',

' Mic. ')

description,

wsv.preferred_grade grade_code,

wsv.attribute1 packing_number,

wsv.picked_quantity rq_1,

wsv.picked_quantity2 rq_2,

wsv.requested_quantity_uom rq_uom1,

wsv.requested_quantity_uom2 rq_uom2,

mtl.segment3 id,

mtl.segment4 od,

(SELECT attribute4

FROM mtl_lot_numbers

WHERE lot_number = wsv.lot_number

AND organization_id = '106')

LENGTH,

--( select ROUND(NET_WEIGHT) from wsh_new_deliveries where delivery_id= wsv.delivery_id) NET_WEIGHT,

ROUND (wsv.gross_weight) gross_weight,

TO_CHAR (wsv.net_weight,

'9G999D0',

'NLS_NUMERIC_CHARACTERS = '',.''')

net_weight,

(SELECT no_of_joints

FROM xxmax_slitting_output

WHERE roll_number = wsv.lot_number) -- AND organization_id = '101')

no_of_joints,

(SELECT TO_CHAR (y.slitting_date, 'DDMMYYYY') production_date

FROM xxmax_slitting_output z, xxmax_slitting_headers y

WHERE y.doc_id = z.doc_id

AND z.roll_number = wsv.lot_number)

production_date,

(SELECT TO_CHAR (y.slitting_date, 'HHMMSS') production_date

FROM xxmax_slitting_output z, xxmax_slitting_headers y

WHERE y.doc_id = z.doc_id

AND z.roll_number = wsv.lot_number)

production_time,

jcio.customer_id,

h.attribute3 consignee_name,

h.attribute4 consignee_addr

FROM oe_order_headers_all h,

hz_cust_accounts cust_acct,

oe_order_lines_all ol,

hz_parties party,

hz_cust_site_uses_all ship_su,

hz_locations ship_loc,

hz_party_sites ship_ps,

wsh_deliverables_v wsv,

hz_cust_acct_sites_all ship_cas,

jai_cmn_cus_addresses jcio,

mtl_system_items mtl

WHERE h.order_number = p_order_number

AND wsv.delivery_id = p_delivery_id

AND h.sold_to_org_id = cust_acct.cust_account_id

AND cust_acct.party_id = party.party_id

AND h.header_id = ol.header_id

AND h.ship_to_org_id = ship_su.site_use_id

AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id

AND ship_cas.party_site_id = ship_ps.party_site_id

AND ship_ps.location_id = ship_loc.location_id

AND ol.ship_from_org_id = mtl.organization_id

AND ol.inventory_item_id = mtl.inventory_item_id

AND mtl.inventory_item_id = wsv.inventory_item_id

AND mtl.organization_id = wsv.organization_id

AND ol.line_id = wsv.source_line_id

AND h.header_id = wsv.source_header_id

AND wsv.customer_id = cust_acct.cust_account_id

AND jcio.customer_id = cust_acct.cust_account_id

AND jcio.address_id = ship_cas.cust_acct_site_id

AND jcio.address_id > 0

ORDER BY wsv.attribute1;

BEGIN

FOR i IN lnt_shipping_out

LOOP

-- Inserting data into staging table

INSERT

INTO apps.maxlnt_vibac_pallet_table (vibac_pallet_sequence,

excise_invoice_no,

production_number,

vibac_order_number,

vibac_order_line_number,

former_order,

film_type,

film_user,

guage,

slitter_number,

slitting_batch,

blade_position,

defect1,

defect2,

defect3,

status,

mother_reel_code,

mother_reel_weight,

mother_reel_length,

mother_reel_height,

mother_reel_length_product,

reel_grade,

extrusion_production,

metal_production,

daughter_reel_packaging,

treatment,

vibac_product_code,

number_of_reels_per_pallet,

pallet_base,

pallet_height,

xyear,

delivery_number,

customer_name,

customer_number,

delivery_detail_id,

delivery_line_id,

invoice_to_location,

invoice_to_address1,

invoice_to_address2,

invoice_to_address3,

invoice_to_address4,

delivery_address,

city,

postal_code,

state,

province,

transporter,

freight_terms_code,

lr_type,

load,

ec_code,

cst_reg_no,

header_id,

order_number,

lot_number,

film,

width,

core_weight,

width1,

description,

grade_code,

packing_number,

rq_1,

rq_2,

rq_uom1,

rq_uom2,

id,

od,

LENGTH,

gross_weight,

net_weight,

no_of_joints,

production_date,

production_time,

customer_id,

consignee_name,

consignee_addr)

VALUES (

'P' || 'X' || i.xyear

|| RPAD (

SUBSTR (

LPAD (

LTRIM (TO_CHAR (max_lnt_reel_seq_c.NEXTVAL),

' '),

'0'),

6),

' '),

trim(i.excise_invoice_no),

i.production_number,

i.vibac_order_number,

i.vibac_order_line_number,

i.former_order,

i.film_type,

i.film_user,

i.guage,

i.slitter_number,

i.slitting_batch,

i.blade_position,

i.defect1,

i.defect2,

i.defect3,

i.status,

i.mother_reel_code,

i.mother_reel_weight,

i.mother_reel_length,

i.mother_reel_height,

i.mother_reel_length_product,

i.reel_grade,

i.extrusion_production,

i.metal_production,

i.daughter_reel_packaging,

i.treatment,

i.vibac_product_code,

i.number_of_reels_per_pallet,

i.pallet_base,

i.pallet_height,

i.xyear,

i.delivery_number,

i.customer_name,

i.customer_number,

i.delivery_detail_id,

i.delivery_line_id,

i.invoice_to_location,

i.invoice_to_address1,

i.invoice_to_address2,

i.invoice_to_address3,

i.invoice_to_address4,

i.delivery_address,

i.city,

i.postal_code,

i.state,

i.province,

i.transporter,

i.freight_terms_code,

i.lr_type,

i.load,

i.ec_code,

i.cst_reg_no,

i.header_id,

i.order_number,

i.lot_number,

i.film,

i.width,

i.core_weight,

i.width1,

i.description,

i.grade_code,

i.packing_number,

i.rq_1,

i.rq_2,

i.rq_uom1,

i.rq_uom2,

i.id,

i.od,

i.LENGTH,

i.gross_weight,

i.net_weight,

i.no_of_joints,

i.production_date,

i.production_time,

i.customer_id,

i.consignee_name,

i.consignee_addr);

COMMIT;

END LOOP;

DBMS_OUTPUT.put_line (' Total Reocrd(s) inserted ' || SQL%ROWCOUNT);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (' Error in Below Package');

DBMS_OUTPUT.put_line (

'LOKA_VIBAC_ELECTRONIC_INVOICES.LNT_VIBAC_PALLET_INSERTION');

DBMS_OUTPUT.put_line (

' Error Information => ' || SQLCODE || SQLERRM);

END lnt_vibac_pallet_insertion;

PROCEDURE lnt_vibac_reel_export (errbuf OUT VARCHAR2,

retcode OUT VARCHAR2,

-- p_order_number IN VARCHAR2,

p_delivery_id IN VARCHAR2)

CURSOR lnt_shipping_out

SELECT vibac_reel_sequence,

vibac_pallet_sequence,

production_number,

vibac_order_number,

vibac_order_line_number,

former_order,

film_type,

film_user,

guage,

slitter_number,

slitting_batch,

blade_position,

defect1,

defect2,

defect3,

status,

mother_reel_code,

mother_reel_weight,

mother_reel_length,

mother_reel_height,

mother_reel_length_product,

reel_grade,

extrusion_production,

metal_production,

vibac_product_code,

daughter_reel_packaging,

treatment,

excise_invoice_no,

xyear,

delivery_number,

delivery_detail_id,

delivery_line_id,

customer_name,

customer_number,

invoice_to_location,

invoice_to_address1,

invoice_to_address2,

invoice_to_address3,

invoice_to_address4,

delivery_address,

city,

postal_code,

state,

province,

transporter,

freight_terms_code,

lr_type,

load,

ec_code,

cst_reg_no,

header_id,

order_number,

lot_number,

film,

width,

core_weight,

width1,

description,

grade_code,

packing_number,

rq_1,

rq_2,

rq_uom1,

rq_uom2,

id,

od,

LENGTH,

net_weight,

no_of_joints,

production_date,

production_time,

customer_id,

consignee_name,

consignee_addr,

vibac_so_number,

vibac_line_number

FROM maxlnt_vibac_reel_table

WHERE --order_number = p_order_number

--AND

delivery_number = p_delivery_id

ORDER BY vibac_reel_sequence;

xx_excise_invoice_no VARCHAR2 (200);

outpointer UTL_FILE.file_type;

BEGIN

SELECT DISTINCT

REPLACE (

REPLACE (REPLACE (excise_invoice_no, 'EX/', ' '),

'E',

' '),

'E/',

' '),

'/',

' ')

INTO xx_excise_invoice_no

FROM jai_om_wsh_lines_all jowla, jai_cmn_cus_addresses jcca

WHERE jowla.delivery_id IN (SELECT delivery_id

FROM wsh_new_deliveries

WHERE name = p_delivery_id)

AND jowla.customer_id = jcca.customer_id;

outpointer :=

UTL_FILE.fopen (

'/u02/maxprod/db/tech_st/11.2.0/appsutil/outbound/MAXPROD_proddb',

'801100BOB'

|| TO_CHAR (SYSDATE, 'YYYYMMDD')

|| '_______'

|| TRIM(xx_excise_invoice_no)

|| '.txt',

'W');

FOR i IN lnt_shipping_out

LOOP

UTL_FILE.put_line (

outpointer,

RPAD (NVL (SUBSTR (i.production_number, 1, 9), ' '), 9, ' ')

|| RPAD (

SUBSTR (

LPAD (LTRIM (TO_CHAR (i.VIBAC_REEL_SEQUENCE), ' '),

'0'),

9),

' ')

|| RPAD (

SUBSTR (

LPAD (LTRIM (TO_CHAR (i.VIBAC_PALLET_SEQUENCE), ' '),

'0'),

9),

' ')

|| RPAD (NVL (SUBSTR (i.vibac_so_number, 1, 6), ' '), 6, ' ')

|| RPAD (NVL (SUBSTR (i.vibac_line_number, 1, 2), ' '), 2, ' ')

|| RPAD (NVL (SUBSTR (i.former_order, 1, 9), ' '), 9, ' ')

|| RPAD (NVL (SUBSTR (i.film_type, 1, 1), ' '), 1, ' ')

|| RPAD (NVL (SUBSTR (i.treatment, 1, 1), ' '), 1, ' ')

|| RPAD (NVL (SUBSTR (i.film_user, 1, 1), ' '), 1, ' ')

|| RPAD (NVL (SUBSTR (i.guage, 1, 2), ' '), 2, ' ')

|| RPAD (NVL (SUBSTR (i.slitter_number, 1, 1), ' '), 1, ' ')

|| RPAD (NVL (SUBSTR (i.production_date, 1, 8), ' '), 8, ' ')

|| RPAD (NVL (SUBSTR (i.production_time, 1, 8), ' '), 8, ' ')

|| RPAD (NVL (SUBSTR (i.LENGTH, 1, 5), ' '), 5, ' ')

|| RPAD (

SUBSTR (LPAD (LTRIM (TO_CHAR (i.width), ' '), 4, '0'),

4),

' ')

|| RPAD (NVL (SUBSTR (i.id, 1, 3), ' '), 3, ' ')

|| RPAD (

SUBSTR (

LPAD (LTRIM (TO_CHAR (i.core_weight), ' '), 3, '0'),

3),

' ')

|| RPAD (

SUBSTR (LPAD (LTRIM (TO_CHAR (i.od), ' '), 4, '0'), 1, 4),

' ')

|| RPAD (

SUBSTR (LPAD (LTRIM (TO_CHAR (i.net_weight), ' '), 6, '0'),

6),

' ')

|| RPAD (NVL (SUBSTR (i.slitting_batch, 1, 2), ' '), 2, ' ')

|| RPAD (NVL (SUBSTR (i.blade_position, 1, 2), ' '), 2, ' ')

|| RPAD (NVL (SUBSTR (i.defect1, 1, 3), ' '), 3, ' ')

|| RPAD (NVL (SUBSTR (i.defect2, 1, 3), ' '), 3, ' ')

|| RPAD (NVL (SUBSTR (i.defect3, 1, 3), ' '), 3, ' ')

|| RPAD (NVL (SUBSTR (i.status, 1, 3), ' '), 3, ' ')

|| RPAD (NVL (SUBSTR (i.mother_reel_code, 1, 9), ' '), 9, ' ')

|| RPAD (NVL (SUBSTR (i.mother_reel_weight, 1, 4), ' '), 4, ' ')

|| RPAD (NVL (SUBSTR (i.mother_reel_length, 1, 5), ' '), 5, ' ')

|| RPAD (NVL (SUBSTR (i.mother_reel_height, 1, 4), ' '), 4, ' ')

|| RPAD (NVL (SUBSTR (i.mother_reel_length_product, 1, 2), ' '),

' ')

|| RPAD (NVL (SUBSTR (i.daughter_reel_packaging, 1, 1), ' '),

' ')

|| RPAD (NVL (SUBSTR (i.vibac_product_code, 1, 18), ' '),

18,

' ')

|| RPAD (NVL (SUBSTR (i.reel_grade, 1, 1), ' '), 1, ' ')

|| RPAD (NVL (SUBSTR (i.no_of_joints, 1, 1), ' '), 1, ' ')

|| RPAD (NVL (SUBSTR (i.extrusion_production, 1, 10), ' '),

10,

' ')

|| RPAD (NVL (SUBSTR (i.metal_production, 1, 10), ' '), 10, ' '));

END LOOP;

UTL_FILE.fclose (outpointer);

EXCEPTION

WHEN UTL_FILE.invalid_mode

THEN

raise_application_error (-20051, 'Invalid Mode Parameter');

WHEN UTL_FILE.invalid_path

THEN

raise_application_error (-20052, 'Invalid File Location');

WHEN UTL_FILE.access_denied

THEN

raise_application_error (-20062, 'File Access Denied By');

WHEN UTL_FILE.invalid_offset

THEN

raise_application_error (-20063, 'FSEEK Param Less Than 0');

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'Unknown UTL_FILE Error');

raise_application_error (-20099,

'Unknown UTL_FILE Error' || SQLERRM);

END lnt_vibac_reel_export;

PROCEDURE lnt_vibac_pallet_export (ERRBUF OUT VARCHAR2,

RETCODE OUT VARCHAR2,

-- p_order_number IN VARCHAR2,

p_delivery_id IN VARCHAR2) --,

CURSOR lnt_shipping_out

SELECT vibac_pallet_sequence,

production_number,

vibac_order_number,

vibac_order_line_number,

former_order,

film_type,

film_user,

guage,

slitter_number,

slitting_batch,

blade_position,

defect1,

defect2,

defect3,

status,

mother_reel_code,

mother_reel_weight,

mother_reel_length,

mother_reel_height,

mother_reel_length_product,

reel_grade,

extrusion_production,

metal_production,

daughter_reel_packaging,

treatment,

vibac_product_code,

number_of_reels_per_pallet,

pallet_base,

pallet_height,

excise_invoice_no,

xyear,

delivery_number,

customer_name,

customer_number,

delivery_detail_id,

delivery_line_id,

invoice_to_location,

invoice_to_address1,

invoice_to_address2,

invoice_to_address3,

invoice_to_address4,

delivery_address,

city,

postal_code,

state,

province,

transporter,

freight_terms_code,

lr_type,

load,

ec_code,

cst_reg_no,

header_id,

order_number,

lot_number,

film,

width,

core_weight,

width1,

description,

grade_code,

packing_number,

rq_1,

rq_2,

rq_uom1,

rq_uom2,

id,

od,

LENGTH,

gross_weight,

net_weight,

no_of_joints,

production_date,

production_time,

customer_id,

consignee_name,

consignee_addr

FROM maxlnt_vibac_pallet_table

WHERE --order_number =p_order_number

--AND

delivery_number = p_delivery_id

ORDER BY vibac_pallet_sequence;

xx_excise_invoice_no VARCHAR2 (200);

outpointer UTL_FILE.file_type;

BEGIN

SELECT DISTINCT

REPLACE (

REPLACE (REPLACE (excise_invoice_no, 'EX/', ' '),

'E',

' '),

'E/',

' '),

'/',

' ')

INTO xx_excise_invoice_no

FROM jai_om_wsh_lines_all jowla, jai_cmn_cus_addresses jcca

WHERE jowla.delivery_id IN (SELECT delivery_id

FROM wsh_new_deliveries

WHERE name = p_delivery_id)

AND jowla.customer_id = jcca.customer_id;

outpointer :=

UTL_FILE.fopen (

'/u02/maxprod/db/tech_st/11.2.0/appsutil/outbound/MAXPROD_proddb',

'801100PAL'

|| TO_CHAR (SYSDATE, 'YYYYMMDD')

|| '_______'

|| TRIM(xx_excise_invoice_no)

|| '.txt',

'W');

FOR i IN lnt_shipping_out

LOOP

UTL_FILE.put_line (

outpointer,

RPAD (

SUBSTR (

LPAD (LTRIM (TO_CHAR (i.VIBAC_PALLET_SEQUENCE), ' '),

'0'),

9),

' ')

|| RPAD (NVL (SUBSTR (i.pallet_base, 1, 4), ' '), 4, ' ')

|| RPAD (NVL (SUBSTR (i.pallet_height, 1, 4), ' '), 4, ' ')

|| RPAD (NVL (SUBSTR (i.daughter_reel_packaging, 1, 1), ' '),

' ')

|| RPAD (NVL (SUBSTR (i.number_of_reels_per_pallet, 1, 2), ' '),

' ')

|| RPAD (

SUBSTR (

LPAD (LTRIM (TO_CHAR (i.gross_weight), ' '), 4, '0'),

4),

' ')

|| RPAD (

SUBSTR (LPAD (LTRIM (TO_CHAR (i.net_weight), ' '), 6, '0'),

6),

' ')

|| RPAD (NVL (SUBSTR (i.production_date, 1, 8), ' '), 8, ' ')

|| RPAD (NVL (SUBSTR (i.production_time, 1, 8), ' '), 8, ' ')

|| RPAD (NVL (SUBSTR (i.vibac_product_code, 1, 18), ' '),

18,

' ')

|| RPAD (NVL (SUBSTR (i.reel_grade, 1, 1), ' '), 1, ' '));

END LOOP;

UTL_FILE.fclose (outpointer);

EXCEPTION

WHEN UTL_FILE.invalid_mode

THEN

raise_application_error (-20051, 'Invalid Mode Parameter');

WHEN UTL_FILE.invalid_path

THEN

raise_application_error (-20052, 'Invalid File Location');

WHEN UTL_FILE.access_denied

THEN

raise_application_error (-20062, 'File Access Denied By');

WHEN UTL_FILE.invalid_offset

THEN

raise_application_error (-20063, 'FSEEK Param Less Than 0');

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'Unknown UTL_FILE Error');

raise_application_error (-20099,

'Unknown UTL_FILE Error' || SQLERRM);

END lnt_vibac_pallet_export;

END loka_vibac_electronic_invoices;

Step 2: Run Step1, Step2 Concurrent Programme to Load the data into tables.

Step 3: Create Form to Enter Vibac Sale Order Number

Step 4 : Run Step3, Step4 Concurrent Programme to Export Electronic Invoices

Step 5 : Electronic Invoice File Generation

ORACLE PROJECTS

MENUS

Thursday, 16 April 2015

Electronic Invoice Generation for Vibac Spa - Italy