Oracle Apps Ocean

Thursday, December 11, 2014

Cancel Back orders

1. Code for Cancel Back orders [OM] with the help of standerd API(Ineer loop Cursor).
- Cancel partial quntity and back order foe specific org.

CREATE

OR REPLACE PACKAGE BODY APPS.xxwms_oe_backorder

AS

PROCEDURE kvwms_cancel_orders (

errbuf_out
OUT VARCHAR2,

retcode_out
OUT VARCHAR2,

p_order_number
IN VARCHAR2,

p_org_id
IN NUMBER,

p_email_id
IN VARCHAR2

)

v_qty

NUMBER DEFAULT 0;

l_user_id

NUMBER;

l_resp_id

NUMBER;

l_appl_id

NUMBER;

l_return_status

VARCHAR2 (1000);

--INPUT VARIABLES FOR PROCESS_ORDER API

l_header_rec_in

oe_order_pub.header_rec_type;

l_line_tbl_in

oe_order_pub.line_tbl_type;

--l_header_rec1:=OE_ORDER_PUB.G_MISS_HEADER_REC;

-- t_line_tbl1(1) := OE_ORDER_PUB.G_MISS_LINE_REC;

--l_msg_data VARCHAR2 (1000);

l_action_request_tbl_in

oe_order_pub.request_tbl_type;

--OUTPUT VARIABLES FOR PROCESS_ORDER API

l_header_rec_out

oe_order_pub.header_rec_type;

l_line_tbl_out

oe_order_pub.line_tbl_type;

l_header_val_rec_out

oe_order_pub.header_val_rec_type;

l_header_adj_tbl_out

oe_order_pub.header_adj_tbl_type;

l_header_adj_val_tbl_out

oe_order_pub.header_adj_val_tbl_type;

l_header_price_att_tbl_out

oe_order_pub.header_price_att_tbl_type;

l_header_adj_att_tbl_out

oe_order_pub.header_adj_att_tbl_type;

l_header_adj_assoc_tbl_out

oe_order_pub.header_adj_assoc_tbl_type;

l_header_scredit_tbl_out

oe_order_pub.header_scredit_tbl_type;

l_header_scredit_val_tbl_out

oe_order_pub.header_scredit_val_tbl_type;

l_line_val_tbl_out

oe_order_pub.line_val_tbl_type;

l_line_adj_tbl_out

oe_order_pub.line_adj_tbl_type;

l_line_adj_val_tbl_out

oe_order_pub.line_adj_val_tbl_type;

l_line_price_att_tbl_out

oe_order_pub.line_price_att_tbl_type;

l_line_adj_att_tbl_out

oe_order_pub.line_adj_att_tbl_type;

l_line_adj_assoc_tbl_out

oe_order_pub.line_adj_assoc_tbl_type;

l_line_scredit_tbl_out

oe_order_pub.line_scredit_tbl_type;

l_line_scredit_val_tbl_out

oe_order_pub.line_scredit_val_tbl_type;

l_lot_serial_tbl_out

oe_order_pub.lot_serial_tbl_type;

l_lot_serial_val_tbl_out

oe_order_pub.lot_serial_val_tbl_type;

l_action_request_tbl_out

oe_order_pub.request_tbl_type;

l_msg_index_out

NUMBER (10);

l_chr_program_unit_name

VARCHAR2 (100);

l_ret_status

VARCHAR2 (1000) := NULL;

l_msg_count

NUMBER := 0;

l_msg_data

VARCHAR2 (20000);

l_api_version

NUMBER := 1.0;

v_audit

NUMBER;

v_out_file_name

VARCHAR2 (400);

v_msg_text

VARCHAR2 (4000);

v_send_email

NUMBER;

v_error_msg

VARCHAR2 (4000);

CURSOR get_order_details (p_order_number IN VARCHAR2, p_org_id IN NUMBER

-- ,p_customer_number IN VARCHAR2

)

SELECT DISTINCT oh.order_number order_number, ol.line_number,

.line_id, wdd.released_status, hp.party_name,

.party_number, wdd.organization_id

FROM oe_order_headers_all oh,

oe_order_lines_all ol

mtl_system_items msi

mtl_parameters mp

wsh_delivery_details wdd

hz_cust_accounts hca

hz_parties hp

hz_party_sites hps

hz_cust_acct_sites_all hcasa

hz_cust_site_uses_all hcsua

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND ol.ship_tolerance_below = 100

AND wdd.released_status in ('B','S') --changed added 'S' Status

AND oh.booked_flag = 'Y'

AND oh.order_number =

NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_org_id

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.source_line_id = ol.line_id

AND wdf.released_status NOT IN ('B', 'R', 'D')

--Added logic for Ready to release status

)

--Added logic for Ready to release status ie. wdf.RELEASED_STATUS = 'R'

UNION

SELECT DISTINCT oh.order_number order_number, ol.line_number,

.line_id, wdd.released_status, hp.party_name,

.party_number, wdd.organization_id

FROM oe_order_headers_all oh,

oe_order_lines_all ol

mtl_system_items msi

mtl_parameters mp

wsh_delivery_details wdd

hz_cust_accounts hca

hz_parties hp

hz_party_sites hps

hz_cust_acct_sites_all hcasa

hz_cust_site_uses_all hcsua

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND ol.ship_tolerance_below = 100

AND wdd.released_status = 'R'

AND oh.booked_flag = 'Y'

AND oh.order_number =

NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_org_id

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.released_status NOT IN ('B', 'R', 'D'));

CURSOR b (p_1 IN VARCHAR2, p_2 IN NUMBER)

SELECT ol.line_id, oh.order_number

FROM oe_order_headers_all oh,

oe_order_lines_all ol

mtl_system_items msi

mtl_parameters mp

wsh_delivery_details wdd

hz_cust_accounts hca

hz_parties hp

hz_party_sites hps

hz_cust_acct_sites_all hcasa

hz_cust_site_uses_all hcsua

wsh_delivery_assignments wds

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND wds.delivery_detail_id = wdd.delivery_detail_id

AND ol.ship_tolerance_below = 100

AND released_status NOT IN ('R', 'S')

AND wdd.released_status = 'B'

AND oh.booked_flag = 'Y'

AND oh.order_number = p_1

-- i.order_number --10060173

-- NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_2

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.source_line_id = ol.line_id

AND wdf.released_status NOT IN ('B', 'R', 'D')

--Added logic for Ready to release status

)

--Aded logic for Ready to release status ie. wdf.RELEASED_STATUS = 'R'

UNION

SELECT ol.line_id, oh.order_number

FROM oe_order_headers_all oh,

oe_order_lines_all ol

mtl_system_items msi

mtl_parameters mp

wsh_delivery_details wdd

hz_cust_accounts hca

hz_parties hp

hz_party_sites hps

hz_cust_acct_sites_all hcasa

hz_cust_site_uses_all hcsua

wsh_delivery_assignments wds

WHERE mp.organization_id = msi.organization_id

AND ol.header_id = oh.header_id

AND ol.inventory_item_id = msi.inventory_item_id

AND wdd.source_line_id = ol.line_id

AND wdd.source_header_id = oh.header_id

AND hca.party_id = hp.party_id

AND hp.party_id = hps.party_id

AND wds.delivery_detail_id = wdd.delivery_detail_id

AND hps.party_site_id = hcasa.party_site_id

AND hcasa.cust_account_id = hca.cust_account_id

AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id

AND hca.cust_account_id = wdd.customer_id

AND ol.ship_tolerance_below = 100

AND released_status NOT IN ('R', 'S')

--AND wdd.released_status = 'R'

AND oh.booked_flag = 'Y'

AND oh.order_number = p_1 --10060173

--NVL (p_order_number, oh.order_number)

AND wdd.organization_id = p_2 --p_org_id

AND EXISTS (

SELECT 1

FROM wsh_delivery_details wdf

WHERE wdf.source_header_id = oh.header_id

AND wdf.released_status NOT IN ('B', 'R', 'D'));

BEGIN

DBMS_OUTPUT.put_line

('start 1');

/* SELECT user_id

INTO l_user_id

FROM fnd_user

WHERE user_name = 'RPADHI'; */

SELECT fcr.requested_by user_id

INTO l_user_id

--,fcr.responsibility_id,

--fcr.responsibility_application_id

FROM apps.fnd_concurrent_requests fcr

WHERE request_id = fnd_global.conc_request_id;

fnd_file.put_line (fnd_file.LOG, 'User ID : ' || l_user_id);

v_out_file_name

:= 'o' || fnd_global.conc_request_id || '.out';

SELECT responsibility_id, application_id

INTO l_resp_id, l_appl_id

FROM fnd_responsibility_vl

WHERE responsibility_name LIKE 'KV OM Inquiry';

--added responsibility name on 9-may by kamal

-- INITIALIZE ENVIRONMENT

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

fnd_file.put_line (fnd_file.LOG, 'Responsibility ID : ' || l_resp_id);

fnd_file.put_line (fnd_file.LOG, 'Application ID : ' || l_appl_id);

--v_msg_text := 'Order_number | Line_number | Line_id| Status | Message ';

--DBMS_OUTPUT.put_line ('User_id'||l_user_id);

v_msg_text

RPAD ('Order_number', 15)

'|'

RPAD ('Line_number', 12)

'|'

RPAD ('Line_id', 10)

'|'

RPAD ('Status', 7)

'|'

'Message';

v_send_email

:= 0;

fnd_file.put_line (fnd_file.output, v_msg_text);

DBMS_OUTPUT.put_line

(v_msg_text);

FOR i IN get_order_details (p_order_number, p_org_id)

LOOP

--**************************************************************************************************************************

BEGIN

SELECT NVL (SUM (requested_quantity), 0)

INTO v_qty

FROM wsh_delivery_details wdd

WHERE wdd.released_status IN ('S', 'Y', 'C', 'I', 'L', 'N')

AND wdd.source_line_id = i.line_id

AND wdd.source_code = 'OE'; --added on 15 may

--DBMS_OUTPUT.put_line ('Stage2');

fnd_file.put_line (1, 'Ordered Quantity ' || '-' || v_qty);

EXCEPTION

WHEN OTHERS

THEN

v_qty

:= 0;

fnd_file.put_line (1, 'Quantity not Available' || SQLERRM);

END;

-- INITIALIZE LINE RECORD

l_line_tbl_in

(1) := oe_order_pub.g_miss_line_rec;

l_line_tbl_in

(1).ordered_quantity := v_qty;

IF v_qty = 0

THEN

l_line_tbl_in

(1).flow_status_code := 'CANCELLED';

ELSE

l_line_tbl_in

(1).flow_status_code := 'AWAITING_SHIPPING';

END IF;

/********** inner for loop starts here **********/

FOR j IN b (i.order_number, i.organization_id)

LOOP

BEGIN

SELECT NVL (SUM (requested_quantity), 0)

INTO v_qty

FROM wsh_delivery_details wdd

WHERE wdd.released_status IN

('S', 'Y', 'C', 'I', 'L', 'N')

AND wdd.source_line_id = j.line_id

AND wdd.source_code = 'OE'; --added on 15 may

fnd_file.put_line (1, 'Ordered Quantity ' || '-' || v_qty);

EXCEPTION

WHEN OTHERS

THEN

v_qty

:= 0;

fnd_file.put_line (1,

'Quantity not Available' || SQLERRM

);

END;

-- INITIALIZE LINE RECORD

l_line_tbl_in

(1) := oe_order_pub.g_miss_line_rec;

l_line_tbl_in

(1).ordered_quantity := v_qty;

IF v_qty = 0

THEN

l_line_tbl_in

(1).flow_status_code := 'CANCELLED';

ELSE

l_line_tbl_in

(1).flow_status_code := 'AWAITING_SHIPPING';

END IF;

l_line_tbl_in

(1).line_id := j.line_id;

-- l_line_tbl_in (1).FLOW_STATUS_CODE := 'CANCELLED';

l_line_tbl_in

(1).change_reason := 'KVBOCAN';

--Administrative Reason

l_line_tbl_in

(1).change_comments := 'Cancel Order Line';

l_line_tbl_in

(1).operation := oe_globals.g_opr_update;

-- Mandatory Operation to Pass

oe_msg_pub.delete_msg;

oe_order_pub

.process_order

(p_api_version_number => l_api_version,

p_init_msg_list

=> fnd_api.g_false,

p_return_values

=> fnd_api.g_false,

p_action_commit

=> fnd_api.g_false,

p_line_tbl

=> l_line_tbl_in,

x_header_rec

=> l_header_rec_out,

x_header_val_rec

=> l_header_val_rec_out,

x_header_adj_tbl

=> l_header_adj_tbl_out,

x_header_adj_val_tbl

=> l_header_adj_val_tbl_out,

x_header_price_att_tbl

=> l_header_price_att_tbl_out,

x_header_adj_att_tbl

=> l_header_adj_att_tbl_out,

x_header_adj_assoc_tbl

=> l_header_adj_assoc_tbl_out,

x_header_scredit_tbl

=> l_header_scredit_tbl_out,

x_header_scredit_val_tbl

=> l_header_scredit_val_tbl_out,

x_line_tbl

=> l_line_tbl_out,

x_line_val_tbl

=> l_line_val_tbl_out,

x_line_adj_tbl

=> l_line_adj_tbl_out,

x_line_adj_val_tbl

=> l_line_adj_val_tbl_out,

x_line_price_att_tbl

=> l_line_price_att_tbl_out,

x_line_adj_att_tbl

=> l_line_adj_att_tbl_out,

x_line_adj_assoc_tbl

=> l_line_adj_assoc_tbl_out,

x_line_scredit_tbl

=> l_line_scredit_tbl_out,

x_line_scredit_val_tbl

=> l_line_scredit_val_tbl_out,

x_lot_serial_tbl

=> l_lot_serial_tbl_out,

x_lot_serial_val_tbl

=> l_lot_serial_val_tbl_out,

x_action_request_tbl

=> l_action_request_tbl_out,

x_return_status

=> l_ret_status,

x_msg_count

=> l_msg_count,

x_msg_data

=> l_msg_data

);

l_msg_data

:= NULL;

v_error_msg

:= NULL;

IF l_ret_status <> 'S'

THEN

FOR iindx IN 1 .. l_msg_count

LOOP

-- l_msg_data := l_msg_data || ' ' || oe_msg_pub.get (iindx);

oe_msg_pub.get (p_msg_index => iindx,

p_encoded

=> fnd_api.g_false,

p_data

=> l_msg_data,

p_msg_index_out

=> l_msg_index_out

);

v_error_msg

:= l_msg_data || ' ' || l_msg_data;

fnd_file.put_line (fnd_file.LOG, 'in error loop ');

-- DBMS_OUTPUT.put_line ('in error loop ');

COMMIT;

END LOOP;

fnd_file.put_line (fnd_file.LOG,

'***** Backorder Failed *****'

);

--DBMS_OUTPUT.put_line ('Backorder Failed ');

fnd_file.put_line (fnd_file.LOG,

'Sales Order => ' || j.order_number

);

--DBMS_OUTPUT.put_line ('Sales Order => ' || j.order_number);

-- fnd_file.put_line (fnd_file.LOG,

-- 'Line Number => ' || j.line_number

-- );

fnd_file.put_line (fnd_file.LOG,

'Line ID=> '

|| j

.line_id

' Line Cancelation Failed'

);

--DBMS_OUTPUT.put_line ('Line ID=> ' || j.line_id);

--DBMS_OUTPUT.put_line ('Return Status: ' || l_ret_status);

fnd_file.put_line (fnd_file.LOG,

'Return Status: ' || l_ret_status

);

v_send_email

:= 1;

v_error_msg

:= REPLACE (v_error_msg, CHR (13), '-');

v_error_msg

:= REPLACE (v_error_msg, CHR (10), ' ');

v_msg_text

RPAD (j.order_number, 15)

'|'

-- || RPAD (j.line_number, 12)

'|'

RPAD (j.line_id, 10)

'|'

RPAD (l_ret_status, 7)

'|'

|| v_error_msg

;

fnd_file.put_line (fnd_file.output, v_msg_text);

-- DBMS_OUTPUT.put_line (v_msg_text);

ELSE

fnd_file.put_line (fnd_file.LOG,

'***** Backorder Sucess *****'

);

fnd_file.put_line (fnd_file.LOG,

'Sales Order => ' || j.order_number

);

-- DBMS_OUTPUT.put_line

-- ( 'sucess back order:-Sales Order => '

-- || j.order_number

-- );

fnd_file.put_line (fnd_file.LOG,

'Line Number => ' || j.line_id

);

fnd_file.put_line (fnd_file.LOG,

'Line ID=> '

|| i

.line_id

' Line Cancelled Successfully'

);

-- DBMS_OUTPUT.put_line ('sucess:-Line ID=> ' || j.line_id);

fnd_file.put_line (fnd_file.LOG,

'Return Status: ' || l_ret_status

);

DBMS_OUTPUT.put_line

('Return Status: ' || l_ret_status);

-- fnd_file.put_line(fnd_file.log,'Error Message: ' || l_msg_data);

END IF;

COMMIT;

END;

COMMIT;

--**************************************************************************************************************************

END LOOP;

END xxwms_cancel_orders;

Friday, November 7, 2014

Save Exception or Bulk Exceptions in bulk collections

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();
  l_error_count  NUMBER;
  
  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50).id := NULL;
  l_tab(51).id := NULL;
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
      INSERT INTO exception_test
      VALUES l_tab(i);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i || 
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
  END;
END;
/

Number of failures: 2
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

Bulk Binding concept with Example

The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. To test bulk binds using records we first create a test table.

CREATE TABLE forall_test (
  id           NUMBER(10),
  code         VARCHAR2(10),
  description  VARCHAR2(50));

ALTER TABLE forall_test ADD (
  CONSTRAINT forall_test_pk PRIMARY KEY (id));

ALTER TABLE forall_test ADD (
  CONSTRAINT forall_test_uk UNIQUE (code));

The following test compares the time taken to insert 10,000 rows using regular FOR..LOOP and a bulk bind.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

  l_tab    t_forall_test_tab := t_forall_test_tab();
  l_start  NUMBER;
  l_size   NUMBER            := 10000;
BEGIN
  -- Populate collection.
  FOR i IN 1 .. l_size LOOP
    l_tab.extend;

    l_tab(l_tab.last).id          := i;
    l_tab(l_tab.last).code        := TO_CHAR(i);
    l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
  END LOOP;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

  -- Time regular inserts.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_tab.first .. l_tab.last LOOP
    INSERT INTO forall_test (id, code, description)
    VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
  END LOOP;

  DBMS_OUTPUT.put_line('Normal Inserts: ' || 
                       (DBMS_UTILITY.get_time - l_start));
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

  -- Time bulk inserts.  
  l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO forall_test VALUES l_tab(i);

  DBMS_OUTPUT.put_line('Bulk Inserts  : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  COMMIT;
END;
/
Normal Inserts: 305
Bulk Inserts  : 14

PL/SQL procedure successfully completed.

SQL>

Bulk Collect concept with Example

SET SERVEROUTPUT ON
DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();
  l_start  NUMBER;
BEGIN
  -- Time a regular population.
  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT *
                  FROM   bulk_collect_test)
  LOOP
    l_tab.extend;
    l_tab(l_tab.last) := cur_rec;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' || 
                       (DBMS_UTILITY.get_time - l_start));
  
  -- Time bulk population.  
  l_start := DBMS_UTILITY.get_time;

  SELECT *
  BULK COLLECT INTO l_tab
  FROM   bulk_collect_test;

  DBMS_OUTPUT.put_line('Bulk    (' || l_tab.count || ' rows): ' || 
                       (DBMS_UTILITY.get_time - l_start));
END;
/
Regular (42578 rows): 66
Bulk    (42578 rows): 4

PL/SQL procedure successfully completed.

SQL>

Wednesday, January 1, 2014

AR INVOICE( XML REPORT)

=================== AR QUERY INVOICE DETAILS ================

SELECT
RCTA.TRX_NUMBER,RCTA.TRX_DATE,RTT.NAME TERMS,RSA.NAME SALES_REP,RCTA.INVOICE_CURRENCY_CODE,
HCA1.ACCOUNT_NAME SOLD_TO_ACCT,HP1.PARTY_NAME SOLD_TO_CUST,
HCA2.ACCOUNT_NAME BILL_TO_ACCT,HP2.PARTY_NAME BILL_TO_CUST,
HL2.ADDRESS1 BILL_ADD1,HL2.ADDRESS2 BILL_ADD2,HL2.ADDRESS3 BILL_ADD3,HL2.ADDRESS4 BILL_ADD4,
HL2.CITY BILL_CITY,HL2.STATE BILL_STATE,HL2.POSTAL_CODE BILL_POST_CODE,FLV2.MEANING BILL_COUNTRY,HCA3.ACCOUNT_NAME SHIP_TO_ACCT,HP3.PARTY_NAME SHIP_TO_CUST,
HL3.ADDRESS1 SHIP_ADD1,HL3.ADDRESS2 SHIP_ADD2,HL3.ADDRESS3 SHIP_ADD3,HL3.ADDRESS4 SHIP_ADD4,
HL3.CITY SHIP_CITY,HL3.STATE SHIP_STATE,HL3.POSTAL_CODE SHIP_POST_CODE,FLV3.MEANING SHIP_COUNTRY,
HL4.ADDRESS1 REMIT_ADD1,HL4.ADDRESS2 REMIT_ADD2,HL4.ADDRESS3 REMIT_ADD3,HL4.ADDRESS4 REMIT_ADD4,
HL4.CITY REMIT_CITY,HL4.STATE REMIT_STATE,HL4.POSTAL_CODE REMIT_POST_CODE,FLV4.MEANING REMIT_COUNTRY,
RCTLA.DESCRIPTION ITEM_DESC,RCTLA.QUANTITY_INVOICED,RCTLA.UNIT_SELLING_PRICE ITEM_PRICE,RCTLA.TAXABLE_AMOUNT,RCTLA.EXTENDED_AMOUNT
FROM
RA_CUSTOMER_TRX_ALL RCTA,
RA_TERMS_TL RTT
,RA_SALESREPS_ALL RSA
,HZ_CUST_ACCOUNTS HCA1
,HZ_PARTIES HP1
,HZ_CUST_ACCOUNTS HCA2
,HZ_PARTIES HP2
,HZ_CUST_SITE_USES_ALL HCAUA2
,HZ_CUST_ACCT_SITES_ALL HCASA2
,HZ_PARTY_SITES HPS2
,HZ_LOCATIONS HL2
,FND_LOOKUP_VALUES FLV2
,HZ_CUST_ACCOUNTS HCA3
,HZ_PARTIES HP3
,HZ_CUST_SITE_USES_ALL HCAUA3
,HZ_CUST_ACCT_SITES_ALL HCASA3
,HZ_PARTY_SITES HPS3
,HZ_LOCATIONS HL3
,FND_LOOKUP_VALUES FLV3
,HZ_PARTY_SITES HPS4
,HZ_LOCATIONS HL4
,FND_LOOKUP_VALUES FLV4
,RA_CUSTOMER_TRX_LINES_ALL RCTLA
WHERE 1=1
AND RCTA.TRX_NUMBER='12024'
AND RCTA.TERM_ID=RTT.TERM_ID
AND RTT.LANGUAGE=USERENV('LANG')
AND RCTA.PRIMARY_SALESREP_ID=RSA.SALESREP_ID
AND RCTA.ORG_ID=RSA.ORG_ID
AND RCTA.SOLD_TO_CUSTOMER_ID=HCA1.CUST_ACCOUNT_ID
AND HCA1.PARTY_ID=HP1.PARTY_ID
AND RCTA.BILL_TO_CUSTOMER_ID=HCA2.CUST_ACCOUNT_ID
AND HCA2.PARTY_ID=HP2.PARTY_ID
AND RCTA.BILL_TO_SITE_USE_ID=HCAUA2.SITE_USE_ID
AND HCAUA2.CUST_ACCT_SITE_ID=HCASA2.CUST_ACCT_SITE_ID
AND HCASA2.PARTY_SITE_ID=HPS2.PARTY_SITE_ID
AND HPS2.LOCATION_ID=HL2.LOCATION_ID
AND HL2.COUNTRY=FLV2.LOOKUP_CODE
AND FLV2.LOOKUP_TYPE='PER_US_COUNTRY_CODE'
AND RCTA.SHIP_TO_CUSTOMER_ID=HCA3.CUST_ACCOUNT_ID
AND HCA3.PARTY_ID=HP3.PARTY_ID
AND RCTA.SHIP_TO_SITE_USE_ID=HCAUA3.SITE_USE_ID
AND HCAUA3.CUST_ACCT_SITE_ID=HCASA3.CUST_ACCT_SITE_ID
AND HCASA3.PARTY_SITE_ID=HPS3.PARTY_SITE_ID
AND HPS3.LOCATION_ID=HL3.LOCATION_ID
AND HL3.COUNTRY=FLV3.LOOKUP_CODE
AND FLV3.LOOKUP_TYPE='PER_US_COUNTRY_CODE'
AND RCTA.REMIT_TO_ADDRESS_ID=HPS4.PARTY_SITE_ID
AND HPS4.LOCATION_ID=HL4.LOCATION_ID
AND HL4.COUNTRY=FLV4.LOOKUP_CODE
AND FLV4.LOOKUP_TYPE='PER_US_COUNTRY_CODE'
AND RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
AND RCTA.ORG_ID=:P_ORG_ID
AND RCTA.TRX_NUMBER BETWEEN NVL(:P_TRX_FROM,RCTA.TRX_NUMBER) AND NVL(:P_TRX_TO,RCTA.TRX_NUMBER)

based on above query generate the following XML Tags

=================== XML TAGS ===================

<?xml version="1.0" encoding="WINDOWS-1252"?>

<FRDARINVHC>
<LIST_G_TRX_NUMBER>
<G_TRX_NUMBER>
<TRX_NUMBER>12024</TRX_NUMBER>
<TRX_DATE>29-SEP-13</TRX_DATE>
<TERMS>Net 15</TERMS>
<SALES_REP>Green, Suzanne</SALES_REP>
<INVOICE_CURRENCY_CODE>USD</INVOICE_CURRENCY_CODE>
<SOLD_TO_ACCT>ABC Application Software</SOLD_TO_ACCT>
<SOLD_TO_CUST>ABC Application Software</SOLD_TO_CUST>
<BILL_TO_ACCT>ABC Application Software</BILL_TO_ACCT>
<BILL_TO_CUST>ABC Application Software</BILL_TO_CUST>
<BILL_ADD1>536 Madison Avenue</BILL_ADD1>
<BILL_ADD2></BILL_ADD2>
<BILL_ADD3></BILL_ADD3>
<BILL_ADD4></BILL_ADD4>
<BILL_CITY>New York</BILL_CITY>
<BILL_STATE>NY</BILL_STATE>
<BILL_POST_CODE>10012</BILL_POST_CODE>
<BILL_COUNTRY>United States of America</BILL_COUNTRY>
<SHIP_TO_ACCT>A. C. Networks</SHIP_TO_ACCT>
<SHIP_TO_CUST>A. C. Networks</SHIP_TO_CUST>
<SHIP_ADD1>3405 East Bay Blvd.</SHIP_ADD1>
<SHIP_ADD2></SHIP_ADD2>
<SHIP_ADD3></SHIP_ADD3>
<SHIP_ADD4></SHIP_ADD4>
<SHIP_CITY>Provo</SHIP_CITY>
<SHIP_STATE>UT</SHIP_STATE>
<SHIP_POST_CODE>84606</SHIP_POST_CODE>
<SHIP_COUNTRY>United States of America</SHIP_COUNTRY>
<REMIT_ADD1>PO Box 680978</REMIT_ADD1>
<REMIT_ADD2>ATTN: Accounts Receivable</REMIT_ADD2>
<REMIT_ADD3>Vision Corporation</REMIT_ADD3>
<REMIT_ADD4></REMIT_ADD4>
<REMIT_CITY>New York</REMIT_CITY>
<REMIT_STATE>NY</REMIT_STATE>
<REMIT_POST_CODE>10022</REMIT_POST_CODE>
<REMIT_COUNTRY>United States of America</REMIT_COUNTRY>
<LIST_G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>10000</EXTENDED_AMOUNT>
<ITEM_PRICE>1000</ITEM_PRICE>
<TAXABLE_AMOUNT></TAXABLE_AMOUNT>
<QUANTITY_INVOICED>10</QUANTITY_INVOICED>
<ITEM_DESC>5" 35 lbs/ft pup joints</ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>475</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>10000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>1900</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>40000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>400</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>40000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>25</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>10000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>100</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>40000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>100</EXTENDED_AMOUNT>
<ITEM_PRICE></ITEM_PRICE>
<TAXABLE_AMOUNT>10000</TAXABLE_AMOUNT>
<QUANTITY_INVOICED></QUANTITY_INVOICED>
<ITEM_DESC></ITEM_DESC>
</G_ITEM_DESC>
<G_ITEM_DESC>
<EXTENDED_AMOUNT>40000</EXTENDED_AMOUNT>
<ITEM_PRICE>2000</ITEM_PRICE>
<TAXABLE_AMOUNT></TAXABLE_AMOUNT>
<QUANTITY_INVOICED>20</QUANTITY_INVOICED>
<ITEM_DESC>Heating/Air Conditioning Filter 24x16x1</ITEM_DESC>
</G_ITEM_DESC>
</LIST_G_ITEM_DESC>
</G_TRX_NUMBER>
</LIST_G_TRX_NUMBER>
</FRDARINVHC>

based on above tags design the following rtf file

==================== INVOICE DESIGN .rtf FILE =====================

HERE ARE SOME SAMPLE DESIGN

-------------------------------------------

for variable declaration in xml field
ln1= <?xdoxslt:set_variable($_XDOCTX,'ln',0)?>
for increment of the value of variable
ln2= <?xdoxslt:set_variable($_XDOCTX,'ln', xdoxslt:get_variable($_XDOCTX,'ln')+1)?>
for printing value
ln3= <?xdoxslt:get_variable($_XDOCTX,'ln')?>

for running total
variable declaration
sm1= <?xdoxslt:set_variable($_XDOCTX,'sm',0)?>

for increment of value based on EXTENDED_AMOUNT column value
sm2= <? xdoxslt:set_variable($_XDOCTX,'sm',xdoxslt:get_variable($_XDOCTX,'sm')+EXTENDED_AMOUNT)?>

for printing the value
sm3= <?xdoxslt:get_variable($_XDOCTX,'sm')?>

for total invoice amount

sum = <?sum (EXTENDED_AMOUNT)?>

AP PAYMENT CHECK DETAILS (XML REPORT)

============= DATA TEMPLATE FOR CHECK ==============

<?xml version="1.0" encoding="UTF-8" ?>
<dataTemplate name="AP_INV_CHK" description="ITC CHECK" version="1.0">
<dataQuery>
<sqlStatement name="ITC_CHK_QUERY">
<![CDATA[SELECT AIA.INVOICE_NUM,AIPA.AMOUNT CHECK_AMOUNT,AP_AMOUNT_UTILITIES_PKG.ap_convert_number(AIPA.AMOUNT)||' Rupess Only' AMOUNT,AIPA.BANK_ACCOUNT_NUM,ACA.CHECK_NUMBER,
ACA.VENDOR_NAME,ASSA.VENDOR_SITE_CODE
,ASSA.ADDRESS_LINE1,ASSA.ADDRESS_LINE2,ASSA.ADDRESS_LINE3,ASSA.CITY,ASSA.STATE,ASSA.ZIP,ASSA.PROVINCE,ASSA.COUNTRY
,CBBV.BANK_NAME,CBBV.BANK_BRANCH_NAME
,CBBV.ADDRESS_LINE1 BANK_ADDRESS_LINE1
,CBBV.ADDRESS_LINE2 BANK_ADDRESS_LINE2
,CBBV.ADDRESS_LINE3 BANK_ADDRESS_LINE3
,CBBV.CITY BANK_CITY,CBBV.STATE BANK_STATE,CBBV.ZIP BANK_ZIP,CBBV.PROVINCE BANK_PROVINCE,CBBV.COUNTRY BANK_COUNTRY
FROM
AP_INVOICES_ALL AIA
,AP_INVOICE_PAYMENTS_ALL AIPA
,AP_CHECKS_ALL ACA
,AP_SUPPLIER_SITES_ALL ASSA
,CE_BANK_ACCT_USES_ALL CBAUA
,CE_BANK_ACCOUNTS CBA
,CE_BANK_BRANCHES_V CBBV
WHERE 1=1
AND AIA.INVOICE_NUM='ITC40'
AND AIA.INVOICE_ID=AIPA.INVOICE_ID
AND AIPA.CHECK_ID=ACA.CHECK_ID
AND AIA.VENDOR_SITE_ID=ASSA.VENDOR_SITE_ID
AND ACA.CE_BANK_ACCT_USE_ID=CBAUA.BANK_ACCT_USE_ID
AND CBAUA.BANK_ACCOUNT_ID=CBA.BANK_ACCOUNT_ID
AND CBA.BANK_BRANCH_ID=CBBV.BRANCH_PARTY_ID]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="ITC_CHECK" source="ITC_CHK_QUERY">
<element name="INVOICE_NUM" value="INVOICE_NUM" />
<element name="CHECK_AMOUNT" value="CHECK_AMOUNT" />
<element name="BANK_ACCOUNT_NUM" value="BANK_ACCOUNT_NUM" />
<element name="CHECK_NUMBER" value="CHECK_NUMBER" />
<element name="VENDOR_NAME" value="VENDOR_NAME" />
<element name="VENDOR_SITE_CODE" value="VENDOR_SITE_CODE" />
<element name="ADDRESS_LINE1" value="ADDRESS_LINE1" />
<element name="ADDRESS_LINE2" value="ADDRESS_LINE2" />
<element name="ADDRESS_LINE3" value="ADDRESS_LINE3" />
<element name="CITY" value="CITY" />
<element name="STATE" value="STATE" />
<element name="ZIP" value="ZIP" />
<element name="PROVINCE" value="PROVINCE" />
<element name="COUNTRY" value="COUNTRY" />
<element name="BANK_NAME" value="BANK_NAME" />
<element name="BANK_BRANCH_NAME" value="BANK_BRANCH_NAME" />
<element name="BANK_ADDRESS_LINE1" value="BANK_ADDRESS_LINE1" />
<element name="BANK_ADDRESS_LINE2" value="BANK_ADDRESS_LINE2" />
<element name="BANK_ADDRESS_LINE3" value="BANK_ADDRESS_LINE3" />
<element name="BANK_CITY" value="BANK_CITY" />
<element name="BANK_STATE" value="BANK_STATE" />
<element name="BANK_ZIP" value="BANK_ZIP" />
<element name="BANK_PROVINCE" value="BANK_PROVINCE" />
<element name="BANK_COUNTRY" value="BANK_COUNTRY" />
</group>
</dataStructure>
</dataTemplate>

based on above data template the following XML Tags are generated.
======================= XML TAGS FOR CHECK DESIGN ====================
<?xml version="1.0" encoding="UTF-8"?>

<AP_INV_CHK>
<LIST_ITC_CHECK>
<ITC_CHECK>
<INVOICE_NUM>ITC40</INVOICE_NUM>
<CHECK_AMOUNT>108.52</CHECK_AMOUNT>
<AMOUNT>One hundred nine Rupess Only</AMOUNT>
<BANK_ACCOUNT_NUM>10271-17621-619</BANK_ACCOUNT_NUM>
<CHECK_NUMBER>1008</CHECK_NUMBER>
<VENDOR_NAME>3M Health Care</VENDOR_NAME>
<VENDOR_SITE_CODE>CORP HQ1</VENDOR_SITE_CODE>
<ADDRESS_LINE1>1000000 3M Way</ADDRESS_LINE1>
<ADDRESS_LINE2/>
<ADDRESS_LINE3/>
<CITY>St. Paul</CITY>
<STATE>MN</STATE>
<ZIP>55144</ZIP>
<PROVINCE/>
<COUNTRY>US</COUNTRY>
<BANK_NAME>Bank of America</BANK_NAME>
<BANK_BRANCH_NAME>New York</BANK_BRANCH_NAME>
<BANK_ADDRESS_LINE1>6788 Wall Street</BANK_ADDRESS_LINE1>
<BANK_ADDRESS_LINE2/>
<BANK_ADDRESS_LINE3/>
<BANK_CITY>New York</BANK_CITY>
<BANK_STATE>New York</BANK_STATE>
<BANK_ZIP>05443</BANK_ZIP>
<BANK_PROVINCE/>
<BANK_COUNTRY>US</BANK_COUNTRY>
</ITC_CHECK>
</LIST_ITC_CHECK>
<LIST_ITC_CHECK>
<ITC_CHECK>
<INVOICE_NUM>ITC40</INVOICE_NUM>
<CHECK_AMOUNT>20000</CHECK_AMOUNT>
<AMOUNT>Twenty Thousands Rupess Only</AMOUNT>
<BANK_ACCOUNT_NUM>10271-17621-619</BANK_ACCOUNT_NUM>
<CHECK_NUMBER>1009</CHECK_NUMBER>
<VENDOR_NAME>Talasila Gowthami</VENDOR_NAME>
<VENDOR_SITE_CODE>CORP HQ</VENDOR_SITE_CODE>
<ADDRESS_LINE1>100 3M Way</ADDRESS_LINE1>
<ADDRESS_LINE2/>
<ADDRESS_LINE3/>
<CITY>St. Paul</CITY>
<STATE>MN</STATE>
<ZIP>55144</ZIP>
<PROVINCE/>
<COUNTRY>US</COUNTRY>
<BANK_NAME>Bank of America</BANK_NAME>
<BANK_BRANCH_NAME>New York</BANK_BRANCH_NAME>
<BANK_ADDRESS_LINE1>6788 Wall Street</BANK_ADDRESS_LINE1>
<BANK_ADDRESS_LINE2/>
<BANK_ADDRESS_LINE3/>
<BANK_CITY>New York</BANK_CITY>
<BANK_STATE>New York</BANK_STATE>
<BANK_ZIP>05443</BANK_ZIP>
<BANK_PROVINCE/>
<BANK_COUNTRY>US</BANK_COUNTRY>
</ITC_CHECK>
</LIST_ITC_CHECK>
</AP_INV_CHK>

based on the above tags design check as below

===================== CHECK DESIGN IN .rtf FILE ======================

HERE ARE SAMPLE DESIGN

----------------------------------

client has to purchase MICR Code

for free MICR Code refer

http://www.fontspace.com/digital-graphics-labs/micr-encoding

ITEM TRANSACTION INTERFACE

/*========================================FINAL item transaction INTERFACE STARTs here =================================================*/

/******************************** HERE STAGING TABLE DEFINITION ****************************/

DROP TRIGGER ITC_ITI_T;

DROP SEQUENCE ITC_ITI_S;

DROP TABLE ITC_ITI_STG;

CREATE TABLE ITC_ITI_STG(

TRANSACTION_ID NUMBER,

STATUS_FLAG VARCHAR2(2),

ERROR_MESSAGE VARCHAR2(4000),

ORGANIZATION_NAME_LS VARCHAR2(240),

ORGANIZATION_ID NUMBER,

TRANSACTION_DATE_LS DATE,

TRANSACTION_TYPE_NAME_LS VARCHAR2(80),

TRANSACTION_TYPE_ID NUMBER,

ITEM_NAME_LS VARCHAR2(40),

INVENTORY_ITEM_ID NUMBER,

SUBINVENTORY_CODE_LS VARCHAR2(10), -- IF COUNT = 0 THEN INVLAIED CODE

TRANSACTION_UOM_LS VARCHAR2(25),

TRANSACTION_UOM VARCHAR2(3),

TRANSACTION_QUANTITY_LS NUMBER,

DISTRIBUTION_SEGMENT1_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT2_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT3_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT4_LS VARCHAR2(25),

DISTRIBUTION_SEGMENT5_LS VARCHAR2(25),

distribution_account_id number,

SOURCE_CODE VARCHAR2(30), -- FROM HERE FOR OUR PURPOSE

SOURCE_LINE_ID NUMBER,

SOURCE_HEADER_ID NUMBER,

PROCESS_FLAG NUMBER,

TRANSACTION_MODE NUMBER,

LAST_UPDATE_DATE DATE,

LAST_UPDATED_BY NUMBER,

CREATION_DATE DATE,

CREATED_BY NUMBER);

/******************************** HERE SEQUENCE DEFINITION ****************************/

CREATE SEQUENCE ITC_ITI_S;

/******************************** HERE TRIGGER DEFINITION ****************************/

CREATE OR REPLACE TRIGGER ITC_ITI_T

BEFORE INSERT ON ITC_ITI_STG FOR EACH ROW

BEGIN

SELECT ITC_ITI_S.NEXTVAL INTO :NEW.TRANSACTION_ID FROM DUAL;

END ITC_ITI_T;

SELECT * FROM ITC_ITI_STG

truncate table itc_iti_stg

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_DATE_LS,TRANSACTION_TYPE_NAME_LS,

ITEM_NAME_LS,SUBINVENTORY_CODE_LS,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('Vision Operations','28-JUL-2012','Miscellaneous receipt','Soft_Raghu','Stores','Each',10,

'01','520','5250','0000','000')

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_DATE_LS,TRANSACTION_TYPE_NAME_LS,

ITEM_NAME_LS,SUBINVENTORY_CODE_LS,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('Vision Operations','28-JUL-2012','Miscellaneous receipt','Soft_Raghu','Stores','Each',10,

'01','520','5250','0000','000')

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_TYPE_NAME_LS,ITEM_NAME_LS,SUBINVENTORY_CODE_LS

,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('ITC OU','Miscellaneous receipt','Soft_Raghu','Stores','Eah',10,

'01','520','5250','0000','000')

INSERT INTO ITC_ITI_STG(ORGANIZATION_NAME_LS,TRANSACTION_TYPE_NAME_LS,ITEM_NAME_LS,SUBINVENTORY_CODE_LS

,TRANSACTION_UOM_LS,transaction_quantity_ls,

DISTRIBUTION_SEGMENT1_LS,DISTRIBUTION_SEGMENT2_LS,DISTRIBUTION_SEGMENT3_LS,

DISTRIBUTION_SEGMENT4_LS,DISTRIBUTION_SEGMENT5_LS

)

values('ORACLE IMAGES','Miscellaneoreceipt','Soft_Raghu','Stores','Each',10,

'01','520','5250','0000','000')

commit;

select * from ITC_ITI_STG

select * from mtl_transactions_interface

delete mtl_transactions_interface

select count(*) from mtl_sysTEM_ITEMS_B WHERE costing_enabled_flag='Y'

AND inventory_asset_flag='Y'

AND segment1='Soft_Raghu'

AND organization_id=204;

/***************************** HERE PACKAGE DEFINITION STARTS ***********/

-- version:3-aug-2013

create or replace package ITC_ITI_PKG

procedure MAP(errbuf out varchar2, retcode out number);

procedure VALIDATE(errbuf out varchar2, retcode out number);

procedure LOAD(errbuf out varchar2, retcode out number);

procedure CALL_STDPROG(errbuf out varchar2, retcode out number);

procedure ERROR_REPORT(errbuf out varchar2, retcode out number);

procedure CALL_ALLPRODS(errbuf out varchar2, retcode out number);

end ITC_ITI_PKG;

/***************************** HERE PACKAGE DEFINITION ENDS ***********/

/***************************** HERE PACKAGE BODY DEFINITION STARTS ***********/

create or replace package body ITC_ITI_PKG

procedure MAP(errbuf out varchar2, retcode out number)

begin

/**************** UPDATEING STATING TABLE STARTS HERE ****************/

UPDATE ITC_ITI_STG SET

STATUS_FLAG='N',

ERROR_MESSAGE=NULL,

ORGANIZATION_ID=NULL,TRANSACTION_TYPE_ID=NULL,

INVENTORY_ITEM_ID=NULL,

SOURCE_CODE='LEGACY_RAGHU', --HERE OUR PURPOSE definations

SOURCE_LINE_ID=123,

SOURCE_HEADER_ID=123,

PROCESS_FLAG=1,

TRANSACTION_MODE=3,

LAST_UPDATE_DATE=TRUNC(SYSDATE),

LAST_UPDATED_BY=1318,

CREATION_DATE=TRUNC(SYSDATE),

CREATED_BY=1318;

UPDATE ITC_ITI_STG SET TRANSACTION_DATE_LS=TRUNC(SYSDATE)

WHERE TRANSACTION_DATE_LS IS NULL;

UPDATE ITC_ITI_STG SET

STATUS_FLAG='X',

ERROR_MESSAGE='Duplicate Record'

WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM ITC_ITI_STG

GROUP BY ORGANIZATION_NAME_LS,TRANSACTION_TYPE_NAME_LS,ITEM_NAME_LS,

SUBINVENTORY_CODE_LS);

UPDATE ITC_ITI_STG SET STATUS_FLAG='MM',ERROR_MESSAGE='Organization Name is Required'

WHERE ORGANIZATION_NAME_LS IS NULL;

UPDATE ITC_ITI_STG SET STATUS_FLAG='MM',ERROR_MESSAGE=ERROR_MESSAGE

||',Transaction Type Name is Required' WHERE TRANSACTION_TYPE_NAME_LS IS NULL;

UPDATE ITC_ITI_STG SET STATUS_FLAG='MM',ERROR_MESSAGE=ERROR_MESSAGE

||',Item Name is Requied' WHERE ITEM_NAME_LS IS NULL;

/**************** UPDATEING STATING TABLE ENDS HERE ****************/

COMMIT;

end MAP;

procedure VALIDATE(errbuf out varchar2, retcode out number)

v_organization_id number;

v_date_count number;

v_transaction_type_id number;

v_inventory_item_id number;

v_count number;

v_uom_code varchar2(3);

v_acct_id number;

v_acct_status number;

cursor ORG_CUR is

select organization_name_ls,transaction_id from ITC_ITI_STG where status_flag not in('X' ,'MM')

and organization_name_ls is not null;

cursor TRAN_DATE_CUR is

select transaction_date_ls,transaction_id from ITC_ITI_STG where status_flag not in ('X','MM')

and organization_name_ls is not null;

cursor TRAN_TYPE_CUR is

select transaction_type_name_ls,transaction_id from ITC_ITI_STG where status_flag not in('X' ,'MM')

and organization_name_ls is not null

and transaction_type_name_ls is not null;

cursor ITEM_CUR is

select organization_id,item_name_ls,transaction_id from ITC_ITI_STG where status_flag not in('X' ,'MM')

and organization_name_ls is not null

and item_name_ls is not null;

cursor SUI_CODE_CUR is

select organization_id,subinventory_code_ls,transaction_id from ITC_ITI_STG where status_flag not in('X','MM')

and organization_name_ls is not null;

cursor TRAN_UOM_CUR is

select transaction_uom_ls,transaction_id from ITC_ITI_STG where status_flag not in('X','MM')

and transaction_uom_ls is not null;

cursor ACCT_CUR is

select organization_id,item_name_ls,distribution_segment1_ls,distribution_segment2_ls,distribution_segment3_ls,

distribution_segment4_ls,distribution_segment5_ls,distribution_account_id,

transaction_id from ITC_ITI_STG where status_flag not in('X','MM')

and organization_name_ls is not null and distribution_segment1_ls is not null;

begin

/**************** ORGANIZATION VALIDATION STARTS HERE ****************/

for ORG_REC in ORG_CUR

loop

begin

select organization_id into v_organization_id from ORG_ORGANIZATION_DEFINITIONS

where upper(organization_name)=upper(ORG_REC.organization_name_ls);

update ITC_ITI_STG set organization_id=v_organization_id

where transaction_id=ORG_REC.transaction_id;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE' ,error_message='No Organization exist:'

||organization_name_ls where transaction_id=ORG_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message='Too Many Organizations with :'

||organization_name_ls where transaction_id=ORG_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message='Unexcepted error at Organization:'

||organization_name_ls where transaction_id=ORG_REC.transaction_id;

end;

end loop;

/**************** ORGANIZATION VALIDATION ENDS HERE ****************/

/**************** TRANSACTION DATE VALIDATION STARTS HERE ****************/

for TRAN_DATE_REC in TRAN_DATE_CUR

loop

begin

SELECT COUNT(*) into v_date_count FROM GL_PERIOD_STATUSES WHERE 1=1

AND CLOSING_STATUS='O'

AND TO_CHAR(TRAN_DATE_REC.transaction_date_ls,'YYYY') BETWEEN TO_CHAR(START_DATE,'YYYY')

AND TO_CHAR(END_DATE,'YYYY')

AND TO_CHAR(TRAN_DATE_REC.transaction_date_ls,'MM') BETWEEN TO_CHAR(START_DATE,'MM')

AND TO_CHAR(END_DATE,'MM')

AND TO_CHAR(TRAN_DATE_REC.transaction_date_ls,'DD') BETWEEN TO_CHAR(START_DATE,'DD')

AND TO_CHAR(END_DATE,'DD')

AND APPLICATION_ID in (401,101)

GROUP BY effective_period_num;

if v_date_count =0then

update ITC_ITI_STG set status_flag='VE',error_message=error_message

||'Period is not for: '

||transaction_date_ls where transaction_id=TRAN_DATE_REC.transaction_id;

end if;

exception

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message

||'Un-able check Periods Open/Close for: '

||transaction_date_ls where transaction_id=TRAN_DATE_REC.transaction_id;

end;

end loop;

/**************** TRANSACTION DATE VALIDATION ENDS HERE ****************/

/**************** Transaction Type VALIDATION STARTS HERE****************/

for TRAN_TYPE_REC in TRAN_TYPE_CUR

loop

begin

select transaction_type_id into v_transaction_type_id from MTL_TRANSACTION_TYPES

where upper(transaction_type_name)=upper(TRAN_TYPE_REC.transaction_type_name_ls);

update ITC_ITI_STG set transaction_type_id=v_transaction_type_id

where transaction_id=TRAN_TYPE_REC.transaction_id;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||' ,No transaction type exist:'

||transaction_type_name_ls where transaction_id=TRAN_TYPE_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Too many transaction types:'

||transaction_type_name_ls

where transaction_id=TRAN_TYPE_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Unexpect error at transaction type:'

||transaction_type_name_ls where transaction_id=TRAN_TYPE_REC.transaction_id;

end;

end loop;

/**************** Transaction Type VALIDATION ENDS HERE ****************/

/**************** Item VALIDATION starts HERE ****************/

for ITEM_REC in ITEM_CUR

loop

begin

select inventory_item_id into v_inventory_item_id from MTL_SYSTEM_ITEMS_B

where upper(segment1)=upper(ITEM_REC.item_name_ls)

and organization_id=ITEM_REC.organization_id;

update ITC_ITI_STG set inventory_item_id=v_inventory_item_id

where transaction_id=ITEM_REC.transaction_id and organization_id is not null;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||', No Org/Item combination found:'

||item_name_ls where transaction_id=ITEM_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',No many Items:'

||item_name_ls where transaction_id=ITEM_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Unexcept error at Item:'

||item_name_ls where transaction_id=ITEM_REC.transaction_id;

end;

end loop;

/**************** Item VALIDATION ENDS HERE ****************/

/**************** SubInventory Code VALIDATION starts HERE ****************/

for SUI_CODE_REC in SUI_CODE_CUR

loop

begin

select count(*) into v_count from MTL_SECONDARY_INVENTORIES

where organization_id=SUI_CODE_REC.organization_id

and upper(secondary_inventory_name)=upper(SUI_CODE_REC.subinventory_code_ls);

if v_count=0 then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Invalied org/subinventory:'

||subinventory_code_ls where transaction_id=SUI_CODE_REC.transaction_id;

end if;

end;

end loop;

/**************** SubInventory Code VALIDATION ENDS HERE ****************/

/**************** transaction UOM VALIDATION startsS HERE ****************/

for TRAN_UOM_REC in TRAN_UOM_CUR

loop

begin

select uom_code into v_uom_code from MTL_UNITS_OF_MEASURE_TL

where unit_of_measure=TRAN_UOM_REC.transaction_uom_ls;

update ITC_ITI_STG set transaction_uom=v_uom_code

where transaction_id=TRAN_UOM_REC.transaction_id;

exception

when no_data_found then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Invalied UOM:'

||transaction_uom_ls where transaction_id=TRAN_UOM_REC.transaction_id;

when too_many_rows then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Too may UOM:'

||transaction_uom_ls where transaction_id=TRAN_UOM_REC.transaction_id;

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||',Unexecpted at UOM:'

||transaction_uom_ls where transaction_id=TRAN_UOM_REC.transaction_id;

end;

end loop;

/**************** transaction UOM VALIDATION endS HERE ****************/

/**************** Accounting VALIDATION starts HERE ****************/

for ACCT_REC in ACCT_CUR

loop

begin

select count(*) into v_acct_status from mtl_sysTEM_ITEMS_B WHERE costing_enabled_flag='Y'

AND inventory_asset_flag='Y'

AND segment1=ACCT_REC.item_name_ls

AND organization_id=ACCT_REC.organization_id;

if v_acct_status>0 then

select code_combination_id into v_acct_id from GL_CODE_COMBINATIONS where

segment1=ACCT_REC.distribution_segment1_ls

and segment2=ACCT_REC.distribution_segment2_ls

and segment3=ACCT_REC.distribution_segment3_ls

and segment4=ACCT_REC.distribution_segment4_ls

and segment5=ACCT_REC.distribution_segment5_ls;

update ITC_ITI_STG set DISTRIBUTION_ACCOUNT_ID=v_acct_id

where transaction_id=ACCT_REC.transaction_id;

end if;

exception

when others then

update ITC_ITI_STG set status_flag='VE',error_message=error_message||'account not found:'

where transaction_id=ACCT_REC.transaction_id;

end;

end loop;

/**************** Accounting VALIDATION ends HERE ****************/

update ITC_ITI_STG set status_flag='V' where status_flag='N' and error_message is null;

COMMIT;

end VALIDATE;

procedure LOAD(errbuf out varchar2, retcode out number)

cursor LOAD_CUR is

select * from ITC_ITI_STG where status_flag='V';

begin

/**************** loding VALIDATION starts HERE ****************/

for LOAD_REC in LOAD_CUR

loop

begin

INSERT INTO MTL_TRANSACTIONS_INTERFACE(

ORGANIZATION_ID,

TRANSACTION_TYPE_ID,

TRANSACTION_DATE,

INVENTORY_ITEM_ID,

SUBINVENTORY_CODE,

TRANSACTION_UOM,

TRANSACTION_QUANTITY,

DISTRIBUTION_ACCOUNT_ID,

SOURCE_CODE,

SOURCE_LINE_ID,

SOURCE_HEADER_ID,

PROCESS_FLAG,

TRANSACTION_MODE,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

CREATION_DATE,

CREATED_BY)

VALUES(load_rec.ORGANIZATION_ID,

load_rec.TRANSACTION_TYPE_ID,

load_rec.TRANSACTION_DATE_ls,

load_rec.INVENTORY_ITEM_ID,

load_rec.SUBINVENTORY_CODE_ls,

load_rec.TRANSACTION_UOM,

load_rec.TRANSACTION_QUANTITY_ls,

load_rec.DISTRIBUTION_ACCOUNT_ID,

load_rec.SOURCE_CODE,

load_rec.SOURCE_LINE_ID,

load_rec.SOURCE_HEADER_ID,

load_rec.PROCESS_FLAG,

load_rec.TRANSACTION_MODE,

load_rec.LAST_UPDATE_DATE,

load_rec.LAST_UPDATED_BY,

load_rec.CREATION_DATE,

load_rec.CREATED_BY);

exception

when others then

update ITC_ITI_STG set status_flag='LE',error_message=error_message

||',loading failed' where transaction_id=LOAD_REC.transaction_id;

end;

end loop;

/**************** loading VALIDATION ends HERE ****************/

commit;

end LOAD;

procedure CALL_STDPROG(errbuf out varchar2, retcode out number)

v_request_id number;

begin

fnd_global.APPS_INITIALIZE(

user_id=>fnd_global.USER_ID,

resp_id=>fnd_global.resp_id,

resp_appl_id=>fnd_global.RESP_APPL_ID);

v_request_id:=fnd_request.SUBMIT_REQUEST(

application =>'INV',

program =>'INCTCM',

description =>'ITC Concurrent Progarm From Package');

commit;

end CALL_STDPROG;

procedure ERROR_REPORT(errbuf out varchar2, retcode out number)

v_count number;

v_valied number;

v_failed number;

v_duplicate number;

cursor ERR_CUR is

select * from ITC_ITI_STG where status_flag not in ('V');

begin

fnd_file.put_line(fnd_file.output,' ITC ITEM TRANSACTION REPORT');

fnd_file.put_line(fnd_file.output,' ---------------------------');

fnd_file.put_line(fnd_file.output,'');

fnd_file.put_line(fnd_file.output,rpad('Orgnization Name', 25,' ')||

rpad('Item Name',15,' ')||

rpad('Transaction Date', 20,' ')||

rpad('Transaction Type Name',40,' ')||

rpad('Error',450,' '));

fnd_file.put_line(fnd_file.output,rpad('----------------', 25,' ')||

rpad('---------',15,' ')||

rpad('----------------', 20,' ')||

rpad('---------------------',40,' ')||

rpad('------',40,' '));

for ERR_REC in ERR_CUR

loop

begin

fnd_file.put_line(fnd_file.output,rpad(ERR_REC.Organization_Name_ls,25,' ')||

rpad(ERR_REC.Item_name_ls,15,' ')||

rpad(ERR_REC.Transaction_date_ls,20,' ')||

rpad(ERR_REC.Transaction_type_name_ls,40,' ')||

rpad(ERR_REC.Error_message,450,' '));

end;

end loop;

fnd_file.put_line(fnd_file.output,' ----------');

fnd_file.put_line(fnd_file.output,' END OF DATA');

fnd_file.put_line(fnd_file.output,' ----------');

fnd_file.put_line(fnd_file.output,'');

fnd_file.put_line(fnd_file.output,' -------');

fnd_file.put_line(fnd_file.output,' SUMMARY');

fnd_file.put_line(fnd_file.output,' -------');

select count(*) into v_count from ITC_ITI_STG;

fnd_file.put_line(fnd_file.output,' Total Number of Records : ' || v_count);

select count(*) into v_valied from ITC_ITI_STG where status_flag='V';

fnd_file.put_line(fnd_file.output,' Number of Valid Records : ' || v_valied);

select count(*) into v_failed from ITC_ITI_STG where statuS_flag='VE' and Error_message is not null;

fnd_file.put_line(fnd_file.output,' Total Number of invalid Records : ' ||v_failed);

select count(*) into v_failed from ITC_ITI_STG where statuS_flag='X' and Error_message is not null;

fnd_file.put_line(fnd_file.output,' Total Number of duplicate Records : ' ||v_failed);

end ERROR_REPORT;

procedure CALL_ALLPRODS(errbuf out varchar2, retcode out number)

v1 varchar2(90);

v2 number;

begin

ITC_ITI_PKG.MAP(V1,V2);

ITC_ITI_PKG.VALIDATE(V1,V2);

ITC_ITI_PKG.LOAD(V1,V2);

ITC_ITI_PKG.CALL_STDPROG(V1,V2);

ITC_ITI_PKG.ERROR_REPORT(V1,V2);

end CALL_ALLPRODS;

end;

/*========================================FINAL item transaction INTERFACE ends here =================================================*/