1 PACKAGE BODY JAI_RCV_RSL_TRIGGER_PKG AS
2 /* $Header: jai_rcv_rsl_t.plb 120.1 2007/06/22 08:29:07 bgowrava ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARU_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_RCV_RSL_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_RCV_RSL_ARU_T1
11 REM
12 REM +======================================================================+
13 */
14 PROCEDURE ARU_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
15 --*********TRANSACTION_TYPE******************
16 CURSOR c_get_tran_type IS
17 Select transaction_type, nvl(quantity, 0) quantity
18 , routing_header_id -- Vijay Shankar for Bug#3637910
19 from RCV_TRANSACTIONS
20 where transaction_id = (
21 Select max(Transaction_id)
22 FROM RCV_TRANSACTIONS
23 where shipment_header_id = pr_new.shipment_header_id
24 AND SHIPMENT_LINE_ID = pr_new.shipment_line_id
25 );
26
27 --Added by Bgowrava for Bug#6144268
28 CURSOR c_ops_check IS
29 SELECT plt.OUTSIDE_OPERATION_FLAG
30 FROM po_line_types_b plt,
31 po_lines_all pla
32 where plt.line_type_id = pla.line_type_id
33 AND pla.po_line_id = pr_new.po_line_id;
34 v_ops_flag PO_LINE_TYPES_B.OUTSIDE_OPERATION_FLAG%TYPE;
35
36 -- Commented the following cursor by Bgowrava for Bug#6144268
37 /* CURSOR c_item_type( p_organization_id IN NUMBER, p_inv_item_id IN NUMBER) IS
38 SELECT item_type FROM mtl_system_items
39 WHERE organization_id = p_organization_id
40 AND inventory_item_id = p_inv_item_id;*/
41
42 v_item_type MTL_SYSTEM_ITEMS.item_type%TYPE;
43 v_tran_type RCV_TRANSACTIONS.transaction_type%TYPE;
44 v_rcv_tran_qty RCV_TRANSACTIONS.quantity%TYPE;
45
46 -- Vijay Shankar for Bug#3637910
47 v_routing_header_id RCV_TRANSACTIONS.routing_header_id%TYPE;
48
49 BEGIN
50 pv_return_code := jai_constants.successful ;
51 /*------------------------------------------------------------------------------------------
52 CHANGE HISTORY: FILENAME: ja_in_update_57F4.sql
53
54 - This trigger is Only to Handle OSP Transactions for matching with Localization 57F4 forms
55
56 S.No Date Author and Details
57 ------------------------------------------------------------------------------------------
58 1 01-JUN-2001 Satya Added DUAL UOM functionality
59
60 2 29-OCT-2002 Nagaraj.s for Bug2643016
61 As Functionally required, an Update statement is written to update the CR_REG_ENTRY_DATE of
62 the ja_in_57f4_table. This will definitely have implications on Approve 57f4 receipt screen on
63 Modvat claim but since no Modvat claim is available for 57f4 register, this has been approved
64 functionally.
65 3 22-JAN-2003 Vijay Shankar for Bug#2746952, FileVersion# 615.2
66 During the RETURN TO VENDOR transaction for the shipment line, the code is getting executed and
67 return quantity is getting updated. This is happening when a partial receipt is made and then RTV
68 is made for the same
69
70 04 25-MAY-2004 Vijay Shankar for Bug#3637910, FileVersion# 619.1
71 modified the if condition, so that 57F4 forms are updated with returned quantity even if direct delivery
72 case, which is not happening previouly
73
74 05. 29-NOV-2005 Aparajita for bug#4036241. Version#115.1
75
76 Introduced the call to centralized packaged procedure,
77 jai_cmn_utils_pkg.check_jai_exists to check if localization has been installed.
78
79 06 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old DB Entity Names,
80 as required for CASE COMPLAINCE. Version 116.1
81
82 07. 13-Jun-2005 File Version: 116.2
83 Ramananda for bug#4428980. Removal of SQL LITERALs is done
84
85 Dependency:
86 ----------
87
88 Sl No. Bug Dependent on
89 Bug/Patch set Details
90 -------------------------------------------------------------------------------------------------
91 1 4036241 4033992 Call to jai_cmn_utils_pkg.check_jai_exists, whcih was created thru bug
92 4033992.
93 ja_in_util_pkg_s.sql 115.0
94 ja_in_util_pkg_b.sql 115.0
95
96 --------------------------------------------------------------------------------------------------*/
97
98 --if
99 -- jai_cmn_utils_pkg.check_jai_exists (p_calling_object => 'JA_IN_UPDATE_57F4', p_inventory_orgn_id => pr_new.to_organization_id)
100 -- =
101 -- FALSE
102 --then
103 /* India Localization funtionality is not required */
104 -- return;
105 --end if;
106
107
108 OPEN c_get_tran_type;
109 FETCH c_get_tran_type INTO v_tran_type, v_rcv_tran_qty, v_routing_header_id;
110 CLOSE c_get_tran_type;
111
112 -- Commented the following cursor by Bgowrava for Bug#6144268
113 /*OPEN c_item_type(pr_new.to_organization_id, pr_new.item_id);
114 FETCH c_item_type INTO v_item_type;
115 CLOSE c_item_type;*/
116
117 -- Added by bgowrava for Bug#6144268
118 OPEN c_ops_check;
119 FETCH c_ops_check INTO v_ops_flag;
120 CLOSE c_ops_check;
121
122 -- This Check means if ITEM_TYPE is OSP and Latest RCV_TRANSCATION is related to RECEIVE or CORRECT
123 -- IF nvl(v_item_type, 'OP') = 'OP' AND v_tran_type IN ('RECEIVE', 'CORRECT') THEN
124 -- if condition modified by Vijay Shankar for Bug#3637910
125 --IF nvl(v_item_type, 'OP') = 'OP'
126 IF nvl(v_ops_flag, 'N') = 'Y' -- Added by bgowrava
127 AND ( ( v_routing_header_id <> 3 AND v_tran_type IN ('RECEIVE', 'CORRECT')) -- Standard and Inspection routing
128 OR ( v_routing_header_id = 3 AND v_tran_type IN ('DELIVER', 'CORRECT')) -- Direct Delivery routing
129 )
130 THEN
131 jai_po_osp_pkg.update_57f4_on_receiving (
132 pr_new.shipment_header_id,
133 pr_new.shipment_line_id,
134 pr_new.to_organization_id,
135 pr_new.ship_to_location_id,
136 pr_new.item_id,
137 v_tran_type,
138 v_rcv_tran_qty,
139 pr_new.primary_unit_of_measure,
140 pr_old.primary_unit_of_measure,
141 pr_new.unit_of_measure,
142 pr_new.po_header_id,
143 pr_new.po_release_id,
144 pr_new.po_line_id,
145 pr_new.po_line_location_id,
146 pr_new.last_updated_by,
147 pr_new.last_update_login,
148 pr_new.creation_date
149 );
150
151 END IF;
152 END ARU_T1 ;
153
154 END JAI_RCV_RSL_TRIGGER_PKG ;