Intercompany
Goods In Transit Journal Entry
Overview
Goods In Transit (GIT) will come into
picture when goods sent by Sender Organization is not received at Receivers
Organization by closing of the period. In order to tally accounts of Sender Org
and Receiver Org, we need to have GIT Journal should be entered in GL.
This will consider all shipments which are
not received at Receivers Org and create a Journal Entry by considering the
quantity and price from AR invoice along with PO, Item and Quantity information
in GIT journal lines.
Business Rules
- · Alternative sites should have receiver inventory org code mapping
- · Receipt should not created either Manual or through ASN
- · Manual AP invoice should not be created.
- · AR invoice should be generated from the senders to get the Unit price and Qty
- · By considering all the above scenarios GIT Journal should be created in GL one per
SCRIPT
CREATE OR REPLACE PACKAGE APPS.XXEUR_IC_GIT_PKG
AS
gn_sob_id PLS_INTEGER :=
fnd_profile.value('GL_SET_OF_BKS_ID');
gn_sender_org_id
NUMBER;
gn_sender_org_unit
NUMBER;
PROCEDURE
main(
errbuf OUT VARCHAR2
,retcode OUT
NUMBER
,p_organization_id IN NUMBER
,p_supp_site_id IN NUMBER
,p_gl_period IN VARCHAR2
);
FUNCTION
batch_received_status (p_shipment_number IN VARCHAR2,
p_batch_number IN VARCHAR2)
RETURN
NUMBER;
FUNCTION
batch_received_qty (p_shipment_number IN VARCHAR2
,p_batch_number IN VARCHAR2)
RETURN
NUMBER;
END XXEUR_IC_GIT_PKG;
/
CREATE OR REPLACE PACKAGE BODY APPS.XXEUR_IC_GIT_PKG
AS
gn_request_id NUMBER :=apps.fnd_global.conc_request_id;
gn_prog_appl_id NUMBER :=apps.fnd_global.prog_appl_id;
gn_program_id NUMBER :=apps.fnd_global.conc_program_id;
gn_user_id NUMBER :=apps.fnd_global.user_id;
gn_login_id NUMBER :=apps.fnd_global.login_id;
gn_org_id NUMBER :=apps.fnd_global.org_id;
gn_bulk_limit NUMBER :=1000;
gn_bulk_err NUMBER :=0;
gd_sysdate DATE :=SYSDATE;
gc_failure
VARCHAR2(1) := 'E';
gc_success
VARCHAR2(1) := 'V';
g_last_date DATE;
PROCEDURE
write_log
( p_msg IN
VARCHAR2 )
IS
BEGIN
APPS.FND_FILE.PUT_LINE (FND_FILE.LOG , P_MSG ) ;
END write_log
;
PROCEDURE
write_output
( p_msg IN
VARCHAR2 )
IS
BEGIN
dbms_output.put_line (p_msg) ;
APPS.FND_FILE.PUT_LINE (FND_FILE.output , P_MSG ) ;
END
write_output ;
PROCEDURE
main(
errbuf OUT
VARCHAR2
,retcode OUT
NUMBER
,p_organization_id IN NUMBER
,p_supp_site_id IN NUMBER
,p_gl_period IN VARCHAR2
) IS
CURSOR
lcu_shipment(p_sender_io NUMBER,p_sender_ou NUMBER) IS
SELECT
DISTINCT WND.delivery_id shipment_number
,WDD.cust_po_number
,WDD.lot_number
,WDD.inventory_item_id
,SUM(WDD.shipped_quantity)shipped_quantity
,WDD.ship_method_code
,WDD.ship_to_site_use_id
,WDD.organization_id sender_io
,WDD.org_id sender_ou
,TRX_LINE.unit_selling_price
UNIT_PRICE
,TRX.invoice_currency_code currency_code
,SUBSTR(SUP.vendor_name,1,90) vendor_name
,PO.vendor_site_id
FROM
wsh_new_deliveries WND
,wsh_delivery_details WDD
,wsh_delivery_assignments WDS
,hz_cust_accounts HCA
,po_headers_all PO
,wsh_delivery_stops_v WDSV
,ra_customer_trx_lines_all TRX_LINE
,ra_customer_trx_all TRX
,ap_suppliers SUP
WHERE WND.delivery_id =WDS.delivery_id
AND
WDS.delivery_detail_id
=WDD.delivery_detail_id
AND
HCA.cust_account_id = WDD.customer_id
AND WDD.organization_id = nvl(p_sender_io,WDD.organization_id)
AND
WDD.org_id =
nvl(p_sender_ou,WDD.org_id)
AND
HCA.customer_class_code = 'INTERCOMPANY'
AND
SUP.vendor_type_lookup_code = 'INTERCOMPANY'
AND
HCA.attribute_category ='Related Party
Information'
AND
HCA.attribute20 =
TO_CHAR(p_organization_id) --gn_org_id
AND
WDD.cust_po_number = PO.segment1
AND
WND.delivery_id =
WDSV.delivery_id
AND
WDSV.activity_code = 'PU'
AND
TRUNC(WDSV.actual_departure_date) <= g_last_date
AND
TRX_LINE.interface_line_context = 'ORDER ENTRY'
AND
TRX_LINE.interface_line_attribute6 = TO_CHAR(WDD.source_line_id)
AND
TRX_LINE.customer_trx_id = TRX.customer_trx_id
AND
WDD.org_id = TRX.org_id
AND
WDD.org_id = TRX_LINE.org_id
AND
PO.vendor_id =
SUP.vendor_id
AND
NVL(WND.attribute15,'N') <> 'Y'
GROUP BY
WND.delivery_id
,WDD.cust_po_number
,WDD.lot_number
,WDD.inventory_item_id
,WDD.ship_method_code
,WDD.ship_to_site_use_id
,WDD.organization_id
,WDD.org_id
,TRX_LINE.unit_selling_price
,TRX.invoice_currency_code
,vendor_name
,PO.vendor_site_id;
lc_segment1
gl_code_combinations.segment1%TYPE;
lc_segment2
gl_code_combinations.segment2%TYPE;
lc_segment3
gl_code_combinations.segment3%TYPE;
lc_segment4
gl_code_combinations.segment4%TYPE;
lc_segment5
gl_code_combinations.segment5%TYPE;
lc_segment6
gl_code_combinations.segment6%TYPE;
lc_segment7
gl_code_combinations.segment7%TYPE;
lc_segment8
gl_code_combinations.segment8%TYPE;
lc_segment9 gl_code_combinations.segment9%TYPE;
ln_retcode
NUMBER:=-1;
ln_cnt NUMBER;
l_return_status
VARCHAR2(10);
l_msg_count
NUMBER;
l_msg_data VARCHAR2(1000);
lc_desc VARCHAR2(100);
ln_amount
NUMBER;
lc_user_je_category_name
VARCHAR2(50);
lc_user_je_source_name
VARCHAR2(50);
lc_status
VARCHAR2(10);
lc_actual_flag
VARCHAR2(1);
lc_debit_acct
VARCHAR2(50);
ln_position
NUMBER:=0;
ln_set_of_books_id
NUMBER;
lc_credit_acct VARCHAR2(50);
ln_batch_qty
NUMBER;
lc_journal_name
VARCHAR2(100);
lc_item_number
VARCHAR2(25);
lc_status_flag
VARCHAR2(1);
BEGIN
g_last_date := LAST_DAY(TO_DATE (p_gl_period, 'MON-YY'));
ln_position :=1;
write_log(' ----- GIT Program
Start -----');
write_log(' ');
write_output(' ----- GIT
Program Start -----');
ln_cnt:=0;
ln_position :=2;
write_log('gn_sender_org_id -
'||gn_sender_org_id||' - gn_sender_org_unit - '||gn_sender_org_unit);
ln_position :=3;
write_log('gn_sender_org_id->'||gn_sender_org_id);
write_log('gn_sender_org_unit->'||gn_sender_org_unit);
BEGIN
SELECT
OOD.organization_id
,OOD.operating_unit
INTO gn_sender_org_id
,gn_sender_org_unit
FROM ap_supplier_sites_all ASS
,org_organization_definitions OOD
WHERE
OOD.organization_code = ASS.vendor_site_code_alt
AND ASS.vendor_site_id = p_supp_site_id;
EXCEPTION
WHEN
others THEN
gn_sender_org_id :=NULL;
gn_sender_org_unit :=NULL;
END;
FOR
lrec_shipment IN lcu_shipment(gn_sender_org_id,gn_sender_org_unit)
LOOP
lc_status_flag := 'S';
gn_sender_org_id :=
lrec_shipment.sender_io;
gn_sender_org_unit := lrec_shipment.sender_ou;
BEGIN
SELECT primary_vendor_item
INTO lc_item_number
FROM po_approved_supplier_list
WHERE item_id =
lrec_shipment.inventory_item_id
AND USING_ORGANIZATION_ID = gn_sender_org_id;
EXCEPTION WHEN OTHERS
THEN
lc_status_flag := 'E';
write_log('Item not exits in ASL - inventory_item_id
->'||lrec_shipment.inventory_item_id);
END;
IF
lc_status_flag = 'S'
THEN
BEGIN
ln_position :=4;
write_log('lrec_shipment.shipment_number->'||lrec_shipment.shipment_number);
write_log('lrec_shipment.lot_number->'||lrec_shipment.lot_number);
lc_user_je_category_name
:='Goods In Transit';
lc_user_je_source_name
:='GIT';
lc_status
:='NEW';
lc_actual_flag
:='A';
lc_debit_acct :='1582101';
lc_credit_acct :=
'2461501';
ln_position
:=5;
lc_journal_name :=
'GIT - '||lrec_shipment.vendor_name;
SELECT HU.set_of_books_id
,GCC.segment1
,GCC.segment2
,GCC.segment3
,GCC.segment4
,GCC.segment5
,GCC.segment6
,GCC.segment7
,GCC.segment8
,GCC.segment9
INTO ln_set_of_books_id
,lc_segment1
,lc_segment2
,lc_segment3
,lc_segment4
,lc_segment5
,lc_segment6
,lc_segment7
,lc_segment8
,lc_segment9
FROM
gl_code_combinations GCC
,ap_supplier_sites_all ASSA
,hr_operating_units HU
WHERE 1=1
AND GCC.code_combination_id =
ASSA.accts_pay_code_combination_id
AND
HU.organization_id=ASSA.org_id
AND ASSA.vendor_site_id=(SELECT
vendor_site_id
FROM po_headers_all
WHERE
segment1=lrec_shipment.cust_po_number
AND
org_id = p_organization_id);
ln_position :=6;
IF
batch_received_status(lrec_shipment.shipment_number,lrec_shipment.lot_number) =
0 THEN
write_log('Inside if condition->'||lrec_shipment.lot_number);
ln_amount := lrec_shipment.unit_price * lrec_shipment.shipped_quantity;
lc_desc :='PO
No: '||lrec_shipment.cust_po_number||' | Batch No: '||lrec_shipment.lot_number
||' | Price :
'||lrec_shipment.unit_price||' | Qty : '||lrec_shipment.shipped_quantity;
ln_position :=7;
INSERT INTO gl_interface
( status
,set_of_books_id
,user_je_source_name
,user_je_category_name
,accounting_date
,currency_code
,date_created
,created_by
,actual_flag
,segment1
,segment2
,segment3
,segment4
,segment5
,segment6
,segment7
,segment8
,segment9
,reference10
,entered_cr
,entered_dr
,ledger_id
,attribute9
,attribute1
,reference4
)
VALUES ( lc_status
,ln_set_of_books_id
,lc_user_je_source_name
,lc_user_je_category_name
,LAST_DAY(TRUNC(gd_sysdate))
,lrec_shipment.currency_code
,gd_sysdate
,gn_user_id
,lc_actual_flag
,lc_segment1
,lc_segment2
,lc_credit_acct
,lc_segment4
,lc_segment5
,lc_segment6
,lc_segment7
,lc_segment8
,lc_segment9
,lc_desc
,ln_amount
,null
,ln_set_of_books_id
,lrec_shipment.vendor_name
,lc_item_number
,lc_journal_name
);
ln_position :=8;
INSERT INTO gl_interface
( status
,set_of_books_id
,user_je_source_name
,user_je_category_name
,accounting_date
,currency_code
,date_created
,created_by
,actual_flag
,segment1
,segment2
,segment3
,segment4
,segment5
,segment6
,segment7
,segment8
,segment9
,reference10
,entered_cr
,entered_dr
,ledger_id
,attribute9
,attribute1
,reference4
)
VALUES ( lc_status
,ln_set_of_books_id
,lc_user_je_source_name
,lc_user_je_category_name
,LAST_DAY(TRUNC(gd_sysdate))
,lrec_shipment.currency_code
,gd_sysdate
,gn_user_id
,lc_actual_flag
,lc_segment1
,lc_segment2
,lc_debit_acct
,lc_segment4
,lc_segment5
,lc_segment6
,lc_segment7
,lc_segment8
,lc_segment9
,lc_desc
,null
,ln_amount
,ln_set_of_books_id
,lrec_shipment.vendor_name
,lc_item_number
,lc_journal_name
);
ln_cnt :=ln_cnt+1;
ELSE
ln_position :=9;
IF batch_received_qty (lrec_shipment.shipment_number,
lrec_shipment.lot_number) < lrec_shipment.shipped_quantity THEN
ln_batch_qty := lrec_shipment.shipped_quantity- batch_received_qty
(lrec_shipment.shipment_number, lrec_shipment.lot_number);
ln_amount
:=lrec_shipment.unit_price*ln_batch_qty;
lc_desc :='PO
No: '||lrec_shipment.cust_po_number||' | Batch No: '||lrec_shipment.lot_number
||' | Price : '||lrec_shipment.unit_price||' | Qty :
'||ln_batch_qty;
ln_position :=10;
INSERT INTO gl_interface
( status
,set_of_books_id
,user_je_source_name
,user_je_category_name
,accounting_date
,currency_code
,date_created
,created_by
,actual_flag
,segment1
,segment2
,segment3
,segment4
,segment5
,segment6
,segment7
,segment8
,segment9
,reference10
,entered_cr
,entered_dr
,ledger_id
,attribute9
,attribute1
,reference4
)
VALUES ( lc_status
,ln_set_of_books_id
,lc_user_je_source_name
,lc_user_je_category_name
,LAST_DAY(TRUNC(gd_sysdate))
,lrec_shipment.currency_code
,gd_sysdate
,gn_user_id
,lc_actual_flag
,lc_segment1
,lc_segment2
,lc_credit_acct
,lc_segment4
,lc_segment5
,lc_segment6
,lc_segment7
,lc_segment8
,lc_segment9
,lc_desc
,ln_amount
,null
,ln_set_of_books_id
,lrec_shipment.vendor_name
,lc_item_number
,lc_journal_name
);
ln_position :=11;
INSERT INTO gl_interface
( status
,set_of_books_id
,user_je_source_name
,user_je_category_name
,accounting_date
,currency_code
,date_created
,created_by
,actual_flag
,segment1
,segment2
,segment3
,segment4
,segment5
,segment6
,segment7
,segment8
,segment9
,reference10
,entered_cr
,entered_dr
,ledger_id
,attribute9
,attribute1
,reference4
)
VALUES ( lc_status
,ln_set_of_books_id
,lc_user_je_source_name
,lc_user_je_category_name
,LAST_DAY(TRUNC(gd_sysdate))
,lrec_shipment.currency_code
,gd_sysdate
,gn_user_id
,lc_actual_flag
,lc_segment1
,lc_segment2
,lc_debit_acct
,lc_segment4
,lc_segment5
,lc_segment6
,lc_segment7
,lc_segment8
,lc_segment9
,lc_desc
,null
,ln_amount
,ln_set_of_books_id
,lrec_shipment.vendor_name
,lc_item_number
,lc_journal_name
);
ln_position :=12;
ln_cnt :=ln_cnt+1;
END IF;
END
IF;
EXCEPTION
WHEN
OTHERS THEN
write_log('Error In Insert statement at position:'||ln_position||'
Shipment Number:- '||lrec_shipment.shipment_number||' Lot Number:-
'||lrec_shipment.lot_number||' Error Message>>> '||SQLERRM);
write_output('Error In Insert statement at position:'||ln_position||'
Shipment Number:- '||lrec_shipment.shipment_number||' Lot
Number'||lrec_shipment.lot_number||' Error Message>>> '||SQLERRM);
END;
--ln_cnt :=ln_cnt+1;
BEGIN
UPDATE wsh_new_deliveries
SET attribute15 = 'Y'
WHERE delivery_id = lrec_shipment.shipment_number;
END;
END IF;
write_log(RPAD('-', 80, '-' ));
END LOOP;
write_log('Inside if condition->'||ln_cnt);
COMMIT;
write_output(RPAD(' ', 80, ' '));
write_output(' Number of
Records Inserted into GL Interface table :-> ' || ln_cnt*2);
write_log(' ');
write_log(' ------
xxeur_lot_number_status_upd_pkg.MAIN Exit------');
write_log(RPAD('*', 80, '*' ));
write_log(' ');
write_output(' ');
write_output(' ------
xxeur_lot_number_status_upd_pkg Exit------');
write_output(RPAD('*', 80, '*' ));
write_output(' ');
EXCEPTION
WHEN OTHERS THEN
write_log(' Error Occured in The
Procedure MAIN');
write_log(' Error Message Is :-> ' || SQLERRM);
write_output('Error Message Is>>> '||SQLERRM);
write_log(' ');
write_log(' ------
xxeur_lot_number_status_upd_pkg.MAIN Exit------');
write_log(RPAD('*', 80, '*' ));
write_log(' ');
END main;
FUNCTION
batch_received_status (p_shipment_number IN VARCHAR2,
p_batch_number IN VARCHAR2)
RETURN
NUMBER
IS
ln_count
NUMBER:=0;
BEGIN
SELECT
count(1)
INTO
ln_count
FROM
rcv_transactions RT
,rcv_shipment_headers RSH
,rcv_shipment_lines RSL
,rcv_transactions DLRT
,mtl_material_transactions MMT
,mtl_transaction_lot_numbers MTLN
,po_headers_all PO
WHERE
1=1
AND
RT.shipment_header_id =
RSH.shipment_header_id
and
RSH.shipment_num =
p_shipment_number
AND
RT.transaction_type = 'RECEIVE'
AND
DLRT.transaction_type = 'DELIVER'
AND
MTLN.lot_number = p_batch_number
AND
DLRT.po_header_id = RT.po_header_id
AND
DLRT.shipment_line_id =
RT.shipment_line_id
AND
RSL.shipment_header_id =
RSH.shipment_header_id
AND
RT.po_line_id = RSL.po_line_id
AND
DLRT.transaction_id =
MMT.rcv_transaction_id
AND
MTLN.transaction_id =
MMT.transaction_id
AND
PO.po_header_id = RSL.po_header_id
AND
TRUNC(RSH.creation_date) <= g_last_date
AND
RSH.vendor_site_id IN (SELECT DISTINCT ass.vendor_site_id
FROM ap_supplier_sites ass
,org_organization_definitions ood
WHERE
ood.organization_code =
ass.vendor_site_code_alt
AND ood.organization_id = gn_sender_org_id
AND ood.operating_unit = gn_sender_org_unit);
RETURN
ln_count;
EXCEPTION
WHEN
others THEN
RETURN
ln_count;
END
batch_received_status;
FUNCTION
batch_received_qty (p_shipment_number IN VARCHAR2
,p_batch_number IN VARCHAR2)
RETURN
NUMBER
IS
ln_count
NUMBER:=0;
ln_qty NUMBER;
BEGIN
SELECT
NVL(SUM(MTLN.TRANSACTION_QUANTITY),0)
INTO
ln_qty
FROM
rcv_transactions RT
,rcv_shipment_headers RSH
,rcv_shipment_lines RSL
,rcv_transactions DLRT
,mtl_material_transactions MMT
,mtl_transaction_lot_numbers MTLN
,po_headers_all PO
WHERE
1=1
AND
RT.shipment_header_id =
RSH.shipment_header_id
and
RSH.shipment_num =
p_shipment_number
AND
RT.transaction_type = 'RECEIVE'
AND
DLRT.transaction_type = 'DELIVER'
AND
MTLN.lot_number = p_batch_number
AND
DLRT.po_header_id = RT.po_header_id
AND
DLRT.shipment_line_id =
RT.shipment_line_id
AND
RSL.shipment_header_id =
RSH.shipment_header_id
AND
RT.po_line_id = RSL.po_line_id
AND
DLRT.transaction_id =
MMT.rcv_transaction_id
AND
MTLN.transaction_id =
MMT.transaction_id
AND
PO.po_header_id =
RSL.po_header_id
AND
TRUNC(RSH.creation_date) <= g_last_date
AND
RSH.vendor_site_id IN (SELECT DISTINCT ass.vendor_site_id
FROM ap_supplier_sites ass
,org_organization_definitions ood
WHERE
ood.organization_code =
ass.vendor_site_code_alt
AND ood.organization_id = gn_sender_org_id
AND ood.operating_unit = gn_sender_org_unit);
RETURN
ln_qty;
EXCEPTION
WHEN
others THEN
ln_qty
:=0;
RETURN
ln_qty;
END
batch_received_qty;
END XXEUR_IC_GIT_PKG;
/
No comments:
Post a Comment