[Home] [Help]
PACKAGE BODY: APPS.JAI_PO_OSP_PKG
Source
1 PACKAGE BODY jai_po_osp_pkg AS
2 /* $Header: jai_po_osp.plb 120.22 2012/05/23 08:22:31 zxin ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.3 jai_po_osp -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13
14 13-Jun-2005 File Version: 116.4
15 Ramananda for bug#4428980. Removal of SQL LITERALs is done
16
17 10-MAR-2009 bug 8303018 vumaasha
18 Included the issue_date column in the insert statement
19 insert into JAI_PO_OSP_HDRS, with value as sysdate.
20 23-SEP-2011 12977826 by anupgupt
21 description: APPROVE 57F(4) DISPATCH FOPRM NOT ALLOWING
22 TO CREATE 57F(4) CHALLAN IF 1ST CHALL
23 fix: corrected cursor query c_header_exists where
24 conditions grouping
25
26 06-DEC-2011 13541366 Description :ISSUE IN UPDATION OF RETURN QTY AND CANCELLATION
27 OF PRIMARY CHALLAN IN 57F4 FORM
28 fix: corrected cursor query c_header_id where
29 conditions grouping and added cancel_flag condition.
30 Reverted back the fix done for 9626826
31 In cance_osp procedure, in the IF concition added
32 the condition for form number also, if for a challan
33 challan number is generated then only the dispatched qty
34 is updated in the primary challan when the secondary
35 challan is cancelled.
36
37
38 19-APR-2012 13963867 Description - ORIGINAL QTY AND DISPATCH QTY FIELD ARE NOT DEFAULTED
39 CORRECTLY IN 57F4 FORM for operation type RESOURCE
40 Fix - While updating the quantity for type RESOURCE, the original
41 quantity and dispatch qty are calculated based on the required
42 quantity of the job multiplied by the qty per assembly factor.
43 But the required qty of the job itself is derived based on the
44 total po quantity multiplied by the qty per assembly. In the code
45 we are again multiplying the required qty with the assembly rate
46 which is causing the issue. Fixed the same and now the quantities
47 are fetched based on required quantity for RESOURCE type operations
48 ----------------------------------------------------------------------------------------------*/
49
50 PROCEDURE ja_in_57F4_process_header
51 (p_po_header_id po_headers_all.po_header_id%type ,
52 p_po_release_id po_releases_all.po_release_id%type,
53 p_vendor_id po_vendors.vendor_id%type ,
54 p_vendor_site_id po_vendor_sites_all.vendor_site_id%type,
55 p_called_from varchar2
56 )
57 is
58 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_osp_pkg.ja_in_57F4_process_header';
59
60 lv_called_release VARCHAR2(10);
61 lv_called_po VARCHAR2(10);
62 begin
63 /*
64 ----------------------------------------------------------------------------------------------------------------------
65 CHANGE HISTORY: FILENAME: jai_po_osp_pkg.sql
66 S.No Date Author and Details
67 ------------------------------------------------------------------------------------------------------------------------
68
69 1. 08/03/2005 Bug# 4218628 File Version 116.0 (115.1)
70
71 Issue:-
72 For a PO with multiple OSP lines, only the first lines components were part of the 57F4 challan.
73 Fix :-
74 The issue was happening because of the incorrect check done to see if the PO has already been
75 processed.There was a header level check done , because of which only the first line was being
76 picked up.
77
78 This issue has been resolved by making a line level check
79
80 3. 10/05/2005 Vijay Shankar for Bug#4346453. Version: 116.1
81 Code is modified due to the Impact of Receiving Transactions DFF Elimination
82 A 57F4 form is getting created if Generate Excise Invoice value of DFF Field is 'Y'. with DFF Elimination
83 the code is moved from ja_in_create_rcV_57f4 trigger to create_rcv_57f4 procedure of this package
84
85 * High Dependancy for future Versions of this object *
86
87 4. 15/02/2007 Vkaranam for bug#4607506,File version 120.2
88 Forward porting the changes done in 11i bug 4559836(osp receipt errors:-ora-01476: divisor is equal to zero)
89 Changes are done in the cursor c_get_lines.
90
91 5. 23/02/07 bduvarag for bug#4609260,File version 120.3
92 Forward porting the changes done in 11i bug 4404917
93 Changes are done in the cursor c_component_rec
94
95 6. 11/05/2007 CSahoo for bug#5699863, File Version 120.4
96 Forward porting R11i BUG#5620085
97 Added a procedure to cancel an OSP Challan
98 modified the cursors c_check_57f4_exists and c_header_exists.
99
100 7. 9-Feb-2009 Bug 8220196 File version 120.1.12000000.5 / 120.4.12010000.3 / 120.8
101 Issue : Exception thrown when trying to create 57F4 form for a PO.
102 Cause : Variables ln_original_quantity and ln_despatch_quantity
103 (introduced in the fix done for bug 7028169) are not
104 defined for PO Entry and Manual Entry cases, resulting in the
105 "Cannot insert null into ..." error.
106 Fix : Added an ELSE part to define those variables for cases which were
107 not handled.
108
109 8. 16-Jul-2009 Bug 8602495 File version 120.1.12000000.8 / 120.4.12010000.6 / 120.11
110 Issue : The 57F4 form shows the PO item (defined as OSP item), even if
111 it is the first item in routing sequence.
112 Fix : Forward ported changes done for 11i bugs 4680221 and 5017903
113 (corresponding R12 FP bugs - 4940629 and 5072683).
114 Following changes are done:
115 1)Cursor c_get_rout_status will get the data from wip_operations
116 instead of bom_operation_sequences.
117 2)Cursor c_discrete_bill_seq_id (and its use) is removed. Data
118 from the po_dist record will be used instead.
119 3)Modified the filter condition in the cursor of c_component_rec.
120 9. 23-Jul-2009 Bug 8678948 AFTER RTV PRIMARY FORM IN APPROVE 57F4 CHALLAN NOT ABLE TO APPROVE THE DISPATCH.
121 issue : The original_qty column is not populated in the table JAI_PO_OSP_LINES in the procedure
122 create_rcv_57f4
123 Fix: populated rtv_qty in the column original_qty
124 ----------------------------------------------------------------------------------------*/
125 /* Added by Ramananda for removal of SQL LITERALs */
126 lv_called_release := 'RELEASE';
127 lv_called_po := 'PO';
128 jai_cmn_utils_pkg.print_log('b13541366.log','1 Inside ja_in_57F4_process_header begin ');
129
130 For c_Line_rec in
131 (Select distinct po_line_id
132 from po_distributions_all
133 where po_header_id = p_po_header_id
134 and
135 ( ( p_called_from = lv_called_release and po_release_id = p_po_release_id) --'RELEASE' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
136 OR
137 ( p_called_from = lv_called_po) --'PO'
138 )
139 )
140 Loop
141 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','2 Inside for loop po lineid '||c_Line_rec.po_line_id||' called from '||p_called_from||' ');
142 ja_in_57f4_lines_insert
143 (
144 p_po_header_id ,
145 c_Line_rec.po_line_id ,
146 p_po_release_id ,
147 p_vendor_id ,
148 p_vendor_site_id ,
149 p_called_from
150 );
151 End loop;
152 EXCEPTION
153 WHEN OTHERS THEN
154 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
155 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
156 app_exception.raise_exception;
157 end ja_in_57F4_process_header;
158
159 /*------------------------------------------------------------------------------------------------------------*/
160 PROCEDURE ja_in_57f4_lines_insert
161 (p_po_header_id po_headers_all.po_header_id%type ,
162 p_po_line_id po_lines_all.po_line_id%type ,
163 p_po_release_id po_releases_all.po_release_id%type,
164 p_vendor_id po_vendors.vendor_id%type ,
165 p_vendor_site_id po_vendor_sites_all.vendor_site_id%type,
166 p_called_from varchar2
167 )
168 IS
169 cursor c_check_osp_po_distrib(cp_line_type_id number) is
170 select outside_operation_flag
171 from po_line_types_b
172 where line_type_id = cp_line_type_id ;
173
174 lv_called_release VARCHAR2(10);
175 lv_called_po VARCHAR2(10);
176
177 lv_src_release JAI_PO_OSP_HDRS.SOURCE%type;
178 lv_src_po_release JAI_PO_OSP_HDRS.SOURCE%type;
179 lv_src_po JAI_PO_OSP_HDRS.SOURCE%type;
180 lv_src_pur_ord JAI_PO_OSP_HDRS.SOURCE%type;
181
182 cursor c_check_57f4_exists is
183 select 1
184 from JAI_PO_OSP_HDRS hdr , JAI_PO_OSP_LINES lines
185 where hdr.form_id = lines.form_id
186 AND hdr.po_header_id = p_po_header_id
187 AND (
188 ( p_called_from = lv_called_po --'PO' /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
189 AND lines.po_line_id = p_po_line_id /* This condition added by ssumaith - bug# 4218628 */
190 )
191 OR ( hdr.oth_doc_id = p_po_release_id
192 and p_Called_from = lv_called_release --'RELEASE'
193 AND lines.po_line_id = p_po_line_id /* This condition added by ssumaith - bug# 4218628 */
194 )
195 )
196 AND nvl(cancel_flag,'N') = 'N' ;/*5699863 - csahoo */
197
198 CURSOR c_Excise_Flag(cp_org_id NUMBER, cp_item_id NUMBER) IS
199 SELECT excise_flag, item_tariff
200 FROM JAI_INV_ITM_SETUPS
201 WHERE inventory_item_id = cp_item_id
202 AND organization_id = cp_org_id;
203
204 --******************FETCHING FORM ID FROM SEQUENCE************
205 CURSOR c_form_id IS
206 SELECT JAI_PO_OSP_HDRS_S.NEXTVAL
207 FROM dual;
208
209 --******************FETCHING LINE ID FROM SEQUENCE************
210 CURSOR c_line_id IS
211 SELECT JAI_PO_OSP_LINES_S.NEXTVAL
212 FROM dual;
213 --*****************OSP RETURN DAYS****************************
214 CURSOR c_osp_days(cp_org_id NUMBER) IS
215 SELECT osp_return_days, osp_excise_percent
216 FROM JAI_CMN_INVENTORY_ORGS
217 WHERE organization_id = cp_org_id;
218 --******************CHECK IF 57F4 HEADER EXISTS***************** VNK
219 CURSOR c_header_exists(cp_org_id NUMBER, cp_loc_id NUMBER) IS
220 SELECT count(*)
221 FROM JAI_PO_OSP_HDRS
222 WHERE (( po_header_id = p_po_header_id and p_called_from = 'PO')
223 OR ( oth_doc_id = p_po_release_id and p_called_from = 'RELEASE')) -- for bug#12977826 by anupgupt
224 AND organization_id = cp_org_id
225 AND location_id = cp_loc_id
226 AND nvl(cancel_flag,'N') = 'N';/*5699863*/
227 --******************FETCH HEADER ID***************** VNK
228 CURSOR c_header_id(cp_org_id NUMBER, cp_loc_id NUMBER) IS
229 SELECT form_id
230 FROM JAI_PO_OSP_HDRS
231 WHERE (( po_header_id = p_po_header_id and p_called_from = 'PO')
232 OR ( oth_doc_id = p_po_release_id and p_called_from = 'RELEASE')) -- added for bug #13541366
233 AND organization_id = cp_org_id
234 AND location_id = cp_loc_id
235 AND nvl(cancel_flag,'N') = 'N'; -- added for bug #13541366
236
237 /* Bug 7028169. Changed the cursor definition */
238 CURSOR c_get_item_details(cp_org_id NUMBER, cp_item_id NUMBER) IS
239 SELECT *
240 FROM mtl_system_items
241 WHERE organization_id = cp_org_id
242 AND inventory_item_id = cp_item_id;
243
244 --**********************ITEM VALUE*****************************
245 CURSOR c_item_value(cp_item_id NUMBER, cp_vendor NUMBER, cp_vendor_site NUMBER, cp_uom_code Varchar2) IS
246 SELECT pll.operand
247 FROM qp_list_lines_v pll,
248 JAI_CMN_VENDOR_SITES jvs
249 WHERE pll.list_header_id = jvs.price_list_id
250 AND pll.product_attr_value = to_char(cp_item_id)
251 and product_attribute_context = 'ITEM'
252 AND jvs.vendor_id = cp_vendor
253 AND jvs.vendor_site_id = cp_vendor_site
254 AND pll.product_uom_Code = cp_uom_code
255 AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
256 AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
257 --**********************ITEM VALUE1*****************************
258 CURSOR c_item_value1(cp_item_id NUMBER, cp_vendor NUMBER, cp_uom_code varchar2) IS
259 SELECT pll.operand
260 FROM qp_list_lines_v pll,
261 JAI_CMN_VENDOR_SITES jvs
262 WHERE pll.list_header_id = jvs.price_list_id
263 AND pll.product_attr_value = to_char(cp_item_id)
264 AND product_attribute_context = 'ITEM'
265 AND jvs.vendor_id = cp_vendor
266 and jvs.vendor_site_id = 0
267 AND pll.product_uom_Code = cp_uom_code
268 AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
269 AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
270 --**********************ITEM VALUE2*****************************
271 CURSOR c_item_value2(cp_item_id NUMBER, cp_org_id NUMBER) IS
272 SELECT list_price_per_unit
273 FROM mtl_system_items
274 WHERE inventory_item_id = cp_item_id AND organization_id = cp_org_id;
275 --**********************ITEM COST******************************* GSN
276 CURSOR c_item_cost(cp_item_id NUMBER, cp_org_id NUMBER) IS
277 SELECT cic.item_cost
278 FROM CST_ITEM_COSTS CIC,
279 CST_COST_TYPES CCT
280 WHERE cic.cost_type_id = cct.cost_type_id
281 AND cic.inventory_item_id = cp_item_id
282 AND cic.organization_id = cp_org_id
283 AND cct.allow_updates_flag = 2;
284
285
286 cursor c_parent_item_cur ( cp_component_sequence_id number)is
287 select assembly_item_id
288 from bom_bill_of_materials
289 where bill_sequence_id in
290 (
291 select bill_sequence_id
292 from bom_inventory_components
293 where component_Sequence_id = cp_component_sequence_id
294 );
295
296 cursor c_assembly_id_cur ( cp_wip_entity_id number)is
297 select primary_item_id
298 from wip_discrete_jobs
299 where wip_entity_id = cp_wip_entity_id;
300
301
302 cursor c_po_line_cur is
303 SELECT po_line_id , item_id, unit_meas_lookup_code,
304 unit_price , line_type_id
305 FROM po_lines_all
306 WHERE po_line_id = p_po_line_id;
307
308
309 cursor c_location_id is
310 SELECT ship_to_location_id
311 FROM po_line_locations_all
312 WHERE po_header_id = p_po_header_id
313 AND po_line_id = p_po_line_id;
314
315
316 /* to get the routing sequence id*/
317 /*this cursor is commented for bug 8602495*/
318 /*CURSOR c_discrete_bill_seq_id(cp_wip_entity NUMBER) IS
319 SELECT common_routing_sequence_id
320 FROM wip_discrete_jobs
321 WHERE wip_entity_id = cp_wip_entity;*/
322
323 /* to check if this is the first operation. */
324 /*This cursor re-written for bug 8602495*/
325 CURSOR c_get_rout_status(cp_wip_entity_id NUMBER, cp_wip_operation NUMBER) IS
326 SELECT COUNT(1)
327 FROM wip_operations
328 WHERE wip_entity_id = cp_wip_entity_id
329 AND operation_seq_num < cp_wip_operation ;
330
331 CURSOR c_check_reqmt_ops( cp_wip_entity_id po_distributions_all.wip_entity_id%TYPE , cp_wip_op_seq_num po_distributions_all.wip_operation_seq_num%TYPE) IS
332 SELECT count(1)
333 FROM wip_requirement_operations
334 WHERE wip_entity_id = cp_wip_entity_id
335 AND operation_seq_num = cp_wip_op_seq_num
336 AND wip_supply_type <> 6;
337
338 CURSOR c_check_ja_osp(cp_item_id NUMBER) IS
339 SELECT COUNT(1)
340 FROM JAI_PO_OSP_ITM_DTLS dtl
341 WHERE osp_item_id = cp_item_id;
342
343 ln_check_57f4_exists number;
344 ln_form_id number;
345 ln_header_ins_flag number;
346 ln_header number;
347 ln_vendor number;
348 ln_vendor_site number;
349 ln_osp_return_days number;
350 ln_osp_excise_percent number;
351 ln_line_id number;
352 ln_parent_item_id number;
353 ln_assembly_id number;
354 ln_po_distribution_id number;
355 lv_item_uom varchar2(3);
356 ln_item_Value number;
357 ln_excise_rate number;
358 ln_bal_parent_item_qty number;
359 ln_po_qty number;
360 lv_source_code varchar2(1) ;
361 ln_item_unit_price number;
362 lv_osp_po varchar2(1);
363 lv_Excise_flag JAI_INV_ITM_SETUPS.excise_flag%type;
364 lv_tariff_code JAI_INV_ITM_SETUPS.item_tariff%type;
365 rec_po_line_cur c_po_line_cur%rowtype;
366 ln_location_id number;
367 ln_routing_seq_id number;
368 ln_routing_ctr number;
369 ln_ja_ctr NUMBER;
370 ln_reqmt_op_ctr NUMBER;
371
372 --commented fhe change of 9626826 for bug #13541366
373 /*bug 9626826 by nprashar */
374 /*cursor c_get_resource_usage_rate (cp_wip_entity_id number, cp_op_seq_num number, cp_item_id number) is
375 select usage_rate_or_amount
376 from wip_operation_resources
377 where wip_entity_id = cp_wip_entity_id
378 and operation_seq_num = cp_op_seq_num
379 and resource_id in (select resource_id
380 from bom_resources
381 where purchase_item_id = cp_item_id);
382
383 ln_usage_rate number; */
384 /*end bug 9626826 by nprashar */
385
386 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_osp_pkg.ja_in_57f4_lines_insert';
387
388 -- Bug 7028169. Added by Lakshmi Gopalsami
389 r_get_po_item_details c_get_item_details%ROWTYPE;
390 r_get_comp_item_details c_get_item_details%ROWTYPE;
391 ln_original_quantity NUMBER;
392 ln_despatch_quantity NUMBER;
393
394
395
396 BEGIN
397 ln_check_57f4_exists := 0;
398 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','3 Inside insert lines procedure '||' ');
399
400 /* Added by Ramananda for removal of SQL LITERALs */
401 lv_called_release := 'RELEASE';
402 lv_called_po := 'PO';
403
404 open c_check_57f4_exists;
405 fetch c_check_57f4_exists into ln_check_57f4_exists;
406 close c_check_57f4_exists;
407
408 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','4 Inside insert lines ln_check_57f4_exists value '||ln_check_57f4_exists||' ');
409
410 ln_vendor := p_vendor_id;
411 ln_vendor_site := p_vendor_site_id;
412
413
414 if nvl(ln_check_57f4_exists,0) = 1 then
415 return;
416 end if;
417
418 open c_po_line_cur;
419 fetch c_po_line_cur into rec_po_line_cur;
420 close c_po_line_cur;
421
422
423 lv_osp_po := 'N';
424 open c_check_osp_po_distrib(rec_po_line_cur.line_type_id);
425 fetch c_check_osp_po_distrib into lv_osp_po;
426 close c_check_osp_po_distrib;
427
428 if nvl(lv_osp_po,'N') <> 'Y' then
429 goto continue_with_next;
430 end if;
431
432 ln_item_unit_price := rec_po_line_cur.unit_price;
433
434 For c_po_dist In
435 (SELECT SUM(quantity_ordered) quantity_ordered,
436 destination_organization_id orgn_id ,
437 deliver_to_location_id loc_id ,
438 wip_entity_id, wip_operation_seq_num,
439 wip_repetitive_schedule_id, wip_line_id
440 FROM po_distributions_all
441 WHERE po_header_id = p_po_header_id
442 and po_line_id = rec_po_line_cur.po_line_id
443 and
444 ( ( p_called_from = 'RELEASE' and po_release_id = p_po_release_id)
445 OR
446 ( p_called_from = 'PO' )
447 )
448 GROUP BY destination_organization_id,deliver_to_location_id,
449 wip_entity_id, wip_operation_seq_num,
450 wip_repetitive_schedule_id, wip_line_id
451 )
452 Loop
453 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','5 Inside for loop line id'||rec_po_line_cur.po_line_id||' ');
454
455 open c_location_id;
456 fetch c_location_id into ln_location_id;
457 close c_location_id;
458
459 ln_header := 0;
460
461 OPEN c_header_exists( c_po_dist.orgn_id, nvl(c_po_dist.loc_id,ln_location_id));
462 FETCH c_header_exists INTO ln_header;
463 CLOSE c_header_exists;
464
465 OPEN c_check_reqmt_ops(c_po_dist.wip_entity_id , c_po_dist.wip_operation_seq_num );
466 FETCH c_check_reqmt_ops INTO ln_reqmt_op_ctr;
467 CLOSE c_check_reqmt_ops;
468
469 OPEN c_check_ja_osp(rec_po_line_cur.item_id);
470 FETCH c_check_ja_osp INTO ln_ja_ctr;
471 CLOSE c_check_ja_osp;
472
473 IF ln_reqmt_op_ctr IS NULL THEN
474 ln_reqmt_op_ctr := 0;
475 END IF;
476
477 IF ln_ja_ctr IS NULL THEN
478 ln_ja_ctr := 0;
479 END IF;
480 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','6 ln_header '||ln_header||' ');
481
482 IF ln_header = 0 THEN
483 OPEN c_form_id;
484 FETCH c_form_id INTO ln_form_id;
485 CLOSE c_form_id;
486 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','7 form id '||ln_form_id||' ');
487 ln_header_ins_flag := 1;
488 ELSIF ln_header > 0 THEN
489 OPEN c_header_id(c_po_dist.orgn_id, nvl(c_po_dist.loc_id,ln_location_id));
490 FETCH c_header_id INTO ln_form_id;
491 CLOSE c_header_id;
492 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','8 form id '||ln_form_id||' ');
493 ln_header_ins_flag := 0;
494 END IF;
495
496 --commented the change of 9626826 for bug #13541366
497 /*bug 9626826 by nprashar */
498 /* ln_usage_rate := 1;
499
500 open c_get_resource_usage_rate(c_po_dist.wip_entity_id, c_po_dist.wip_operation_seq_num, rec_po_line_cur.item_id);
501 fetch c_get_resource_usage_rate into ln_usage_rate;
502 close c_get_resource_usage_rate;
503
504 if ln_usage_rate is null or ln_usage_rate = 0
505 then
506 ln_usage_rate := 1;
507 end if; */
508 /*end bug 9626826 by nprashar*/
509
510 ln_bal_parent_item_qty := c_po_dist.quantity_ordered;
511 ln_po_qty := c_po_dist.quantity_ordered;
512
513 open c_osp_days(c_po_dist.orgn_id);
514 fetch c_osp_days into ln_osp_return_days , ln_osp_excise_percent;
515 close c_osp_days;
516
517 open c_location_id;
518 fetch c_location_id into ln_location_id;
519 close c_location_id;
520
521 /*below cursor block commented for bug 8602495*/
522 /*open c_discrete_bill_seq_id(c_po_dist.wip_entity_id);
523 fetch c_discrete_bill_seq_id into ln_routing_seq_id;
524 close c_discrete_bill_seq_id;*/
525
526 ln_routing_ctr := 0;
527 open c_get_rout_status(c_po_dist.wip_entity_id, c_po_dist.wip_operation_seq_num); /*changed for bug 8602495*/
528 fetch c_get_rout_status into ln_routing_ctr;
529 close c_get_rout_status;
530
531 /* Bug 7028169. Added by Lakshmi Gopalsami */
532
533 open c_get_item_details(c_po_dist.orgn_id , rec_po_line_cur.item_id);
534 fetch c_get_item_details into r_get_po_item_details ;
535 close c_get_item_details;
536
537 /*
538 insert into JAI_PO_OSP_HDRS
539 */
540 if ln_header_ins_flag = 1 then
541
542 lv_src_release := 'RELEASE' ;
543 lv_src_po_release := 'PO RELEASE';
544 lv_src_po := 'PO';
545 lv_src_pur_ord := 'PURCHASE ORDER';
546
547 INSERT INTO JAI_PO_OSP_HDRS (
548 FORM_ID,
549 PO_HEADER_ID,
550 VENDOR_ID,
551 VENDOR_SITE_ID,
552 PROCESS_TIME,
553 ORGANIZATION_ID,
554 LOCATION_ID,
555 SOURCE,
556 ISSUE_APPROVED,
557 RECEIPT_APPROVED,
558 CANCEL_FLAG,
559 LAST_UPDATE_DATE,
560 LAST_UPDATED_BY,
561 CREATION_DATE,
562 CREATED_BY,
563 LAST_UPDATE_LOGIN,
564 OTH_DOC_ID,
565 PRIMARY_FLAG,
566 ISSUE_DATE -- added for bug 8303018
567 )
568 VALUES
569 (
570 ln_form_id ,
571 p_po_header_id ,
572 ln_vendor ,
573 ln_vendor_site ,
574 ln_osp_return_days ,
575 c_po_dist.orgn_id ,
576 nvl(c_po_dist.loc_id,ln_location_id) ,
577 decode(p_called_from , lv_src_release ,lv_src_po_release,lv_src_po,lv_src_pur_ord), /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
578 'N',
579 'N',
580 'N',
581 sysdate,
582 fnd_global.user_id ,
583 sysdate ,
584 fnd_global.user_id ,
585 fnd_global.login_id,
586 p_po_release_id,
587 'Y',
588 sysdate -- added for bug 8303018
589 );
590
591 end if;
592 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','9 after insert into hdrs ');
593
594 For c_component_rec in
595 ( SELECT required_quantity ,quantity_per_assembly,inventory_item_id ,
596 component_sequence_id , 'W' item_type, comments
597 FROM wip_requirement_operations wro
598 WHERE wro.wip_entity_id = c_po_dist.wip_entity_id
599 AND wro.operation_seq_num = c_po_dist.wip_operation_seq_num
600 AND wip_supply_type <> 6
601 UNION
602 select c_po_dist.quantity_ordered , 1 , rec_po_line_cur.item_id , 1 ,
603 'P' item_type, 'PO Entry' comments
604 from dual
605 where ( ln_routing_ctr > 0 OR ln_reqmt_op_ctr + ln_ja_ctr = 0) /*Bug 4609260*/
606 /*above condition changed for bug 8602495*/
607 union
608 select 1 , dtl.quantity , dtl.item_id , 1 , 'M' item_type,'Manual Entry' comments
609 from JAI_PO_OSP_ITM_DTLS dtl
610 where osp_item_id = rec_po_line_cur.item_id
611 ) /*
612 The wip_supply_type != 6 indicates that phantom kit itself should not come in the 57F4 challan instead the
613 components should feature in the 57F4. Supply Type = 6 indicates a phantom supply type.
614
615 The query after the first union takes care of the scenario where the PO item needs to be part of the 57F4 if it
616 is not the first operation.
617
618 The query after the second union takes care of the manual BOM setup by the user.
619 */
620 Loop
621 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','10 inside lines cursor ');
622
623 ln_line_id := 0;
624 open c_excise_flag(c_po_dist.orgn_id , c_component_rec.inventory_item_id);
625 fetch c_excise_flag into lv_Excise_flag , lv_tariff_code;
626 close c_excise_flag;
627
628 if nvl(lv_Excise_flag,'N') <> 'Y' then
629 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','11 lv_Excise_flag '||lv_Excise_flag||' ');
630 goto continue_with_next_item;
631 end if;
632
633
634 open c_line_id;
635 fetch c_line_id into ln_line_id;
636 close c_line_id;
637
638 ln_parent_item_id := 0;
639
640 /*
641 for the items which are part of the WIP , we get the parent item based on the wip bom tables
642 for the po items and items used in manual bom , the po item is itself the parent item.
643 */
644 if c_component_rec.item_type = 'W' then
645 open c_parent_item_cur(c_component_rec.component_sequence_id);
646 fetch c_parent_item_cur into ln_parent_item_id;
647 close c_parent_item_cur;
648 elsif c_component_rec.item_type in ('P', 'M') then
649 ln_parent_item_id := rec_po_line_cur.item_id;
650 end if;
651 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','12 ln_parent_item_id '||ln_parent_item_id||' ');
652
653 open c_assembly_id_cur(c_po_dist.wip_entity_id);
654 fetch c_assembly_id_cur into ln_assembly_id;
655 close c_assembly_id_cur;
656
657 open c_get_item_details(c_po_dist.orgn_id , c_component_rec.inventory_item_id);
658 fetch c_get_item_details into r_get_comp_item_details ;
659 close c_get_item_details;
660
661 ln_item_Value := Null;
662 OPEN c_item_value(c_component_rec.inventory_item_id,
663 ln_vendor,
664 ln_vendor_site,
665 r_get_comp_item_details.primary_uom_code);
666 FETCH c_item_value INTO ln_item_Value;
667 CLOSE c_item_value;
668
669 IF NVL(ln_item_Value,0) = 0 THEN
670 OPEN c_item_value1(c_component_rec.inventory_item_id,
671 ln_vendor,
672 r_get_comp_item_details.primary_uom_code);
673 FETCH c_item_value1 INTO ln_item_Value;
674 CLOSE c_item_value1;
675
676
677 IF NVL(ln_item_Value,0) = 0 THEN
678 OPEN c_item_value2(c_component_rec.inventory_item_id, c_po_dist.orgn_id);
679 FETCH c_item_value2 INTO ln_item_Value;
680 CLOSE c_item_value2;
681
682 IF NVL(ln_item_Value,0) = 0 THEN
683
684 OPEN c_item_cost(c_component_rec.inventory_item_id, c_po_dist.orgn_id);
685 FETCH c_item_cost INTO ln_item_Value;
686 CLOSE c_item_cost;
687 ln_item_Value := NVL(ln_item_Value,0);
688 /*IF NVL(ln_item_Value,0) = 0 THEN
689 ln_item_Value := NVL(ln_item_unit_price,0);
690 end if;*/
691 END IF;
692 END IF;
693 END IF;
694
695 IF NVL(c_component_rec.inventory_item_id,0) = NVL(ln_parent_item_id,1) THEN
696 ln_item_Value := NVL(ln_item_unit_price,0);
697 END IF;
698
699 lv_source_code := c_component_rec.item_type;
700
701 /* Bug 7028169. Added by Lakshmi Gopalsami
702 * Calculate the original quantity and despatch quantity.
703 */
704
705
706 --removed usage rate changes of 9626826 for bug #13541366
707 IF lv_source_code = 'W' THEN --1
708 IF r_get_po_item_details.outside_operation_uom_type = 'ASSEMBLY' THEN --2
709
710 ln_original_quantity := ln_po_qty * c_component_rec.quantity_per_assembly;
711 ln_despatch_quantity := ln_po_qty * c_component_rec.quantity_per_assembly;
712
713 ELSIF r_get_po_item_details.outside_operation_uom_type = 'RESOURCE' THEN
714 ln_original_quantity := c_component_rec.required_quantity ; --* c_component_rec.quantity_per_assembly; --commented for bug #13963867
715 ln_despatch_quantity := c_component_rec.required_quantity ; --* c_component_rec.quantity_per_assembly; --commented for bug #13963867
716
717 END IF; /* outside_operation_uom_type */ --2
718 /*bug 8220196 - calculate the quantities for PO entry / manual entry cases*/
719 ELSE
720 ln_original_quantity := ln_po_qty * c_component_rec.quantity_per_assembly;
721 ln_despatch_quantity := ln_po_qty * c_component_rec.quantity_per_assembly;
722 /*end bug 8220196*/
723 END IF; /* lv_source_code ='W' */ --1
724 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','13 ln_despatch_quantity '||ln_despatch_quantity||' ');
725
726 INSERT INTO JAI_PO_OSP_LINES (
727 FORM_ID ,
728 LINE_ID ,
729 PO_LINE_ID ,
730 PO_DISTRIBUTION_ID ,
731 ITEM_ID ,
732 WIP_ENTITY_ID ,
733 WIP_LINE_ID ,
734 WIP_REPETITIVE_SCHEDULE_ID ,
735 WIP_OPERATION_SEQUENCE_NUM ,
736 ASSEMBLY_ID ,
737 DESPATCH_QTY ,
738 ITEM_UOM ,
739 ITEM_VALUE ,
740 TARIFF_CODE ,
741 EXCISE_RATE ,
742 LAST_UPDATE_DATE ,
743 LAST_UPDATED_BY ,
744 CREATED_BY ,
745 CREATION_DATE ,
746 LAST_UPDATE_LOGIN ,
747 PARENT_ITEM_ID ,
748 COMP_QTY_PA ,
749 BAL_PARENT_ITEM_QTY ,
750 SOURCE_CODE ,
751 ORIGINAL_QTY,
752 PROCESS_REQD)
753 VALUES (
754 ln_form_id ,
755 ln_line_id ,
756 rec_po_line_cur.po_line_id ,
757 NULL ,
758 c_component_rec.inventory_item_id ,
759 c_po_dist.wip_entity_id ,
760 c_po_dist.wip_line_id ,
761 c_po_dist.wip_repetitive_schedule_id ,
762 c_po_dist.wip_operation_seq_num ,
763 ln_assembly_id ,
764 -- Bug 7028169. Added by Lakshmi Gopalsami
765 ln_despatch_quantity ,
766 r_get_comp_item_details.primary_uom_code ,
767 ln_item_Value ,
768 lv_tariff_code ,
769 nvl(ln_osp_excise_percent,0) ,
770 sysdate ,
771 fnd_global.user_id ,
772 fnd_global.user_id ,
773 sysdate ,
774 fnd_global.login_id ,
775 ln_parent_item_id ,
776 c_component_rec.quantity_per_assembly,
777 ln_bal_parent_item_qty ,
778 lv_source_code ,
779 -- Bug 7028169. Added by Lakshmi Gopalsami
780 ln_original_quantity,
781 c_component_rec.comments
782 );
783
784 << continue_with_next_item >>
785 Null;
786
787 End Loop;
788 End Loop;
789
790
791 << continue_with_next >>
792 Null;
793 EXCEPTION
794 WHEN OTHERS THEN
795 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
796 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
797 app_exception.raise_exception;
798 end ja_in_57f4_lines_insert;
799
800 /*------------------------------------------------------------------------------------------------------------*/
801
802 /* Start - Added by csahoo for bug#5699863 */
803 procedure cancel_osp
804 (p_form_id JAI_PO_OSP_HDRS.form_id%type)
805 is
806 lv_rtv_source VARCHAR2(30) := 'RETURN TO VENDOR';
807
808
809 cursor c_57f4_lines is
810 select hdr.primary_flag, hdr.primary_form_id, dtl.despatch_qty, dtl.po_line_id, dtl.item_id, dtl.form_id, hdr.form_number -- added from number for 13541366
811 from JAI_PO_OSP_HDRS hdr, JAI_PO_OSP_LINES dtl
812 where hdr.form_id = dtl.form_id
813 and hdr.form_id = p_form_id;
814
815 begin
816 /* cancel the OSP Challan */
817 UPDATE JAI_PO_OSP_HDRS
818 SET cancel_flag = 'Y'
819 ,last_update_date = sysdate
820 ,last_updated_by = fnd_global.user_id
821 ,last_update_login= fnd_global.login_id
822 WHERE form_id = p_form_id
823 OR (primary_form_id = p_form_id
824 AND source <> lv_rtv_source
825 );
826
827
828 FOR i_rec in c_57f4_lines LOOP
829
830 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','1.1 Cancel OSP ');
831
832 IF (nvl(i_rec.primary_flag,'N') = 'N' AND nvl(i_rec.form_number,0) <> 0 ) --added form number for 13541366
833 THEN
834
835 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','1.2 Before Update ');
836
837 UPDATE JAI_PO_OSP_LINES set dispatched_qty = dispatched_qty - i_rec.despatch_qty
838 where form_id = i_rec.primary_form_id
839 and item_id = i_rec.item_id
840 and po_line_id = i_rec.po_line_id;
841
842 END IF;
843
844 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','1.3 After Update '||SQL%ROWCOUNT);
845 END LOOP;
846
847
848 end cancel_osp;
849 /* End - Added by csahoo for bug#5699863*/
850
851
852 /***********************************************************************************************/
853
854
855
856
857 /* following procedure is created as part of Receipt/RTV DFF elimination
858 this is a copy of ja_in_create_rcv_57f4 trigger which is removed with DFF elimination
859 this will be invoked only if generate_excise_invoice is true and RTV refers a PO document
860 Vijay Shankar for Bug#4346453. RCV DFF Elim. Enh. */
861 PROCEDURE create_rcv_57f4(
862 p_transaction_id NUMBER,
863 p_process_status OUT NOCOPY VARCHAR2,
864 p_process_message OUT NOCOPY VARCHAR2
865 ) IS
866
867 CURSOR c_base_trx(cp_transaction_id IN NUMBER) IS
868 SELECT shipment_header_id, shipment_line_id, transaction_type, organization_id, location_id,
869 quantity, unit_of_measure, subinventory, vendor_id, vendor_site_id,
870 source_document_code, po_header_id, po_line_id, po_line_location_id, po_release_id
871 FROM rcv_transactions
872 WHERE transaction_id = cp_transaction_id;
873
874 r_base_trx c_base_trx%ROWTYPE;
875
876 CURSOR c_sh_line_info IS
877 SELECT item_id, ship_to_location_id
878 FROM rcv_shipment_lines
879 WHERE shipment_line_id = r_base_trx.shipment_line_id;
880
881 CURSOR c_Excise_Flag (v_item_id NUMBER) IS
882 SELECT excise_flag, item_tariff
883 FROM JAI_INV_ITM_SETUPS
884 WHERE inventory_item_id = v_item_id
885 AND organization_id = r_base_trx.organization_id;
886
887 CURSOR c_po_line_info IS
888 SELECT unit_meas_lookup_code, unit_price
889 FROM po_lines_all
890 WHERE po_line_id = r_base_trx.po_line_id;
891
892 CURSOR c_po_dist_info IS
893 SELECT po_distribution_id, wip_entity_id, wip_line_id,
894 wip_repetitive_schedule_id, wip_operation_seq_num
895 FROM po_distributions_all
896 WHERE line_location_id = r_base_trx.po_line_location_id
897 AND ROWNUM = 1;
898
899 CURSOR c_assembly_id(cp_wip_entity NUMBER) IS
900 SELECT primary_item_id
901 FROM wip_entities
902 WHERE wip_entity_id = cp_wip_entity;
903
904 CURSOR c_osp_days IS
905 SELECT osp_return_days, osp_excise_percent
906 FROM JAI_CMN_INVENTORY_ORGS
907 WHERE organization_id = r_base_trx.organization_id;
908
909 CURSOR c_item_value(cp_item_id NUMBER, cp_uom_code VARCHAR2) IS
910 SELECT pll.list_price
911 FROM so_price_list_lines pll,
912 JAI_CMN_VENDOR_SITES jvs
913 WHERE pll.price_list_id = jvs.price_list_id
914 AND pll.inventory_item_id = cp_item_id
915 AND jvs.vendor_id = r_base_trx.vendor_id
916 AND jvs.vendor_site_id = r_base_trx.vendor_site_id
917 AND Unit_Code = cp_uom_code
918 AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
919 AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
920
921 CURSOR c_item_value1(cp_item_id NUMBER, cp_uom_code VARCHAR2) IS
922 SELECT pll.list_price
923 FROM so_price_list_lines pll,
924 JAI_CMN_VENDOR_SITES jvs
925 WHERE pll.price_list_id = jvs.price_list_id
926 AND pll.inventory_item_id = cp_item_id
927 AND jvs.vendor_id = r_base_trx.vendor_id
928 AND jvs.vendor_site_id = 0
929 AND Unit_Code = cp_uom_code
930 AND NVL( Start_Date_Active, SYSDATE - 1 ) <= SYSDATE
931 AND NVL( End_Date_Active, SYSDATE + 1 ) >= SYSDATE;
932
933 CURSOR c_get_uom_code(cp_measure_code VARCHAR2) IS
934 SELECT UOM_CODE
935 FROM mtl_UNITS_OF_MEASURE
936 WHERE UNIT_OF_MEASURE = cp_measure_code;
937
938 CURSOR v_vend_info(cp_header_id NUMBER) IS
939 SELECT vendor_site_id
940 FROM po_headers_all
941 WHERE po_header_id = cp_header_id;
942
943 -- cbabu for Bug# 2746952
944 v_primary_form_id JAI_PO_OSP_HDRS.primary_form_id%TYPE;
945 CURSOR c_primary_form_id( p_po_header_id IN NUMBER, p_oth_doc_id IN NUMBER) IS
946 SELECT form_id
947 FROM JAI_PO_OSP_HDRS
948 WHERE po_header_id = p_po_header_id
949 AND primary_flag = 'Y'
950 AND issue_approved = 'Y'
951 -- AND receipt_approved = 'N' Sriram - bug# 3303027
952 AND (oth_doc_id = p_oth_doc_id OR oth_doc_id is NULL);
953
954 v_organization_id NUMBER;
955 v_loc NUMBER;
956
957 v_item_id NUMBER;
958 v_loc_id NUMBER;
959 v_uom VARCHAR2(25);
960 v_po_unit_price NUMBER;
961 v_unit_price NUMBER;
962 v_po_dist NUMBER;
963 v_wip_entity NUMBER;
964 v_wip_oprn NUMBER;
965 v_wip_line NUMBER;
966 v_wip_sch NUMBER;
967 v_assembly NUMBER;
968 v_process_time NUMBER;
969 v_excise_rate NUMBER;
970 v_item_tariff VARCHAR2(50);
971 v_form_id NUMBER;
972 v_line_id NUMBER;
973 v_excise_flag VARCHAR2(1);
974
975 v_po_header NUMBER;
976 v_po_release_id NUMBER;
977 v_po_line NUMBER;
978 v_vendor NUMBER;
979 v_vendor_site NUMBER;
980 v_issue_qty NUMBER;
981 v_org_id NUMBER;
982
983 v_created_by NUMBER;
984 v_creation_dt DATE;
985 v_last_upd_dt DATE;
986 v_last_upd_by NUMBER;
987 v_last_upd_lgin NUMBER;
988
989 v_source_code VARCHAR2(1); --File.Sql.35 Cbabu := 'R';
990
991 v_uom_rate number ; -- ssumaith - 3644848
992 v_from_uom_code VARCHAR2(25); -- ssumaith - 3644848
993 v_to_uom_code VARCHAR2(25); -- ssumaith - 3644848
994
995 lv_statement_id VARCHAR2(4);
996
997 lv_source JAI_PO_OSP_HDRS.source%type ;
998 BEGIN
999 /*----------------------------------------------------------------------------------------------------------------- -
1000 FILENAME: ja_in_create_rcv_57F4_trg.sql CHANGE HISTORY:
1001 S.No DD-MON-YYYY Author and Details
1002 ------------------------------------------------------------------------------------------------------------------- -
1003 1. 26/05/2004 ssumaith - bug#3644848 file version 115.1
1004
1005 When rtv is done in a uom different from the PO uom code , the item rate is not getting
1006 recalculated for the rtv uom , instead the uom of the rtv is used with the item rate
1007 of the po uom.
1008
1009 The call to the inv_convert.inv_um_conversion is done to get the conversion factor
1010 and it is used as a factor to calculate the item rate.
1011
1012 2. 29-nov-2004 ssumaith - bug# 4037690 - File version 115.2
1013
1014 Check whether india localization is being used was done using a INR check in every trigger.
1015 This check has now been moved into a new package and calls made to this package from this trigger
1016 If the function JA_IN_UTIL.CHECK_JAI_EXISTS returns true it means INR is the set of books currency ,
1017 Hence if this function returns FALSE , control should return.
1018
1019 3 This Procedure Created from Trigger ja_in_create_rcv_57F4_trg by Vijay Shankar as part of Receipt DFF Elimination
1020 Bug#4346453
1021
1022 ----------------------------------------------------------------------------------------------------------------------*/
1023
1024 lv_statement_id := '1';
1025 v_source_code := 'R';
1026
1027 open c_base_trx(p_transaction_id);
1028 fetch c_base_trx into r_base_trx;
1029 close c_base_trx;
1030
1031 v_created_by := fnd_global.user_id;
1032 v_creation_dt := sysdate;
1033 v_last_upd_dt := sysdate;
1034 v_last_upd_by := fnd_global.user_id;
1035 v_last_upd_lgin := fnd_global.login_id;
1036
1037 v_organization_id := r_base_trx.organization_id;
1038 v_po_header := r_base_trx.po_header_id;
1039 v_po_release_id := r_base_trx.po_release_id;
1040 v_po_line := r_base_trx.po_line_id;
1041 v_vendor := r_base_trx.vendor_id;
1042 v_vendor_site := r_base_trx.vendor_site_id;
1043 v_issue_qty := r_base_trx.quantity;
1044 v_org_id := r_base_trx.organization_id;
1045
1046 lv_statement_id := '2';
1047
1048 OPEN c_po_dist_info;
1049 FETCH c_po_dist_info INTO v_po_dist, v_wip_entity, v_wip_line, v_wip_sch, v_wip_oprn;
1050 CLOSE c_po_dist_info;
1051
1052 -- cbabu for Bug# 2746952
1053 OPEN c_primary_form_id(v_po_header, v_po_release_id);
1054 FETCH c_primary_form_id INTO v_primary_form_id;
1055 CLOSE c_primary_form_id;
1056
1057 lv_statement_id := '3';
1058
1059 IF v_wip_entity IS NOT NULL THEN
1060
1061 OPEN c_sh_line_info;
1062 FETCH c_sh_line_info INTO v_item_id, v_loc_id;
1063 CLOSE c_sh_line_info;
1064
1065 OPEN c_excise_flag(v_item_id);
1066 FETCH c_excise_flag INTO v_excise_flag, v_item_tariff;
1067 CLOSE c_excise_flag;
1068
1069 lv_statement_id := '4';
1070
1071 IF v_excise_flag = 'Y' THEN
1072
1073 lv_statement_id := '5';
1074 OPEN c_po_line_info;
1075 FETCH c_po_line_info INTO v_uom, v_po_unit_price;
1076 CLOSE c_po_line_info;
1077
1078 OPEN c_get_uom_code(v_uom);
1079 FETCH c_get_uom_code INTO v_to_uom_code;
1080 CLOSE c_get_uom_code;
1081
1082 lv_statement_id := '6';
1083 OPEN c_get_uom_code(r_base_trx.unit_of_measure);
1084 FETCH c_get_uom_code INTO v_from_uom_code;
1085 CLOSE c_get_uom_code;
1086
1087 lv_statement_id := '7';
1088 inv_convert.inv_um_conversion(v_from_uom_code,v_to_uom_code,v_item_id,v_uom_rate);
1089 -- bug#3644848
1090 v_uom := r_base_trx.unit_of_measure; -- added by sriram - bug # 3446045
1091
1092 OPEN c_get_uom_code(v_uom);
1093 FETCH c_get_uom_code INTO v_uom;
1094 CLOSE c_get_uom_code;
1095
1096 lv_statement_id := '8';
1097 OPEN c_assembly_id(v_wip_entity);
1098 FETCH c_assembly_id INTO v_assembly;
1099 CLOSE c_assembly_id;
1100
1101 OPEN c_osp_days;
1102 FETCH c_osp_days INTO v_process_time, v_excise_rate;
1103 CLOSE c_osp_days;
1104
1105 lv_statement_id := '9';
1106 OPEN c_item_value(v_item_id,v_uom);
1107 FETCH c_item_value INTO v_unit_price;
1108 CLOSE c_item_value;
1109
1110 OPEN v_vend_info(v_po_header);
1111 FETCH v_vend_info INTO v_vendor_site;
1112 CLOSE v_vend_info;
1113
1114 lv_statement_id := '10';
1115 IF v_unit_price IS NULL THEN
1116 lv_statement_id := '11';
1117 OPEN c_item_value1(v_item_id,v_uom);
1118 FETCH c_item_value1 INTO v_unit_price;
1119 CLOSE c_item_value1;
1120
1121 IF v_unit_price IS NULL THEN
1122 v_unit_price := v_po_unit_price * nvl(v_uom_rate,1); -- ssumaith - 3644848
1123 END IF;
1124
1125 END IF;
1126
1127 lv_statement_id := '12';
1128
1129 lv_source := 'RETURN TO VENDOR' ;
1130 INSERT INTO JAI_PO_OSP_HDRS (
1131 FORM_ID,
1132 OTH_DOC_ID,
1133 PO_HEADER_ID,
1134 VENDOR_ID,
1135 VENDOR_SITE_ID,
1136 PROCESS_TIME,
1137 ORGANIZATION_ID,
1138 LOCATION_ID,
1139 SOURCE,
1140 ISSUE_APPROVED,
1141 RECEIPT_APPROVED,
1142 CANCEL_FLAG,
1143 LAST_UPDATE_DATE,
1144 LAST_UPDATED_BY,
1145 CREATION_DATE,
1146 CREATED_BY,
1147 LAST_UPDATE_LOGIN,
1148 PRIMARY_FORM_ID -- cbabu for Bug# 2746952
1149 ) VALUES (
1150 JAI_PO_OSP_HDRS_S.nextval,
1151 v_po_release_id,
1152 v_po_header,
1153 v_vendor,
1154 v_vendor_site,
1155 v_process_time,
1156 v_org_id,
1157 v_loc_id,
1158 lv_source, --'RETURN TO VENDOR', /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
1159 'N',
1160 'N',
1161 'N',
1162 v_last_upd_dt,
1163 v_last_upd_by,
1164 v_creation_dt,
1165 v_created_by,
1166 v_last_upd_lgin,
1167 v_primary_form_id -- cbabu for Bug# 2746952
1168 ) RETURNING form_id into v_form_id;
1169
1170 lv_statement_id := '14';
1171 INSERT INTO JAI_PO_OSP_LINES (
1172 FORM_ID,
1173 LINE_ID,
1174 PO_LINE_ID,
1175 PO_DISTRIBUTION_ID,
1176 ITEM_ID,
1177 WIP_ENTITY_ID,
1178 WIP_LINE_ID,
1179 WIP_REPETITIVE_SCHEDULE_ID,
1180 WIP_OPERATION_SEQUENCE_NUM,
1181 ASSEMBLY_ID,
1182 ORIGINAL_QTY, /* added for bug 8678948 by vumaasha */
1183 DESPATCH_QTY,
1184 ITEM_UOM,
1185 ITEM_VALUE,
1186 TARIFF_CODE,
1187 EXCISE_RATE,
1188 LAST_UPDATE_DATE,
1189 LAST_UPDATED_BY,
1190 CREATED_BY,
1191 CREATION_DATE,
1192 LAST_UPDATE_LOGIN,
1193 PARENT_ITEM_ID,
1194 COMP_QTY_PA,
1195 BAL_PARENT_ITEM_QTY,
1196 SOURCE_CODE
1197 ) VALUES(
1198 v_form_id,
1199 JAI_PO_OSP_LINES_S.nextval,
1200 v_po_line,
1201 v_po_dist,
1202 v_item_id,
1203 v_wip_entity,
1204 v_wip_line,
1205 v_wip_sch,
1206 v_wip_oprn,
1207 v_assembly,
1208 v_issue_qty,/* added for bug 8678948 vumaasha */
1209 v_issue_qty,
1210 v_uom,
1211 v_unit_price ,
1212 v_item_tariff ,
1213 NVL(v_excise_rate,0),
1214 v_last_upd_dt,
1215 v_last_upd_by,
1216 v_created_by,
1217 v_creation_dt,
1218 v_last_upd_lgin,
1219 v_item_id,
1220 1,
1221 v_issue_qty,
1222 v_source_code
1223 ) RETURNING line_id into v_line_id;
1224
1225 END IF;
1226
1227 END IF;
1228
1229 p_process_status := jai_constants.successful;
1230
1231 EXCEPTION
1232 WHEN OTHERS THEN
1233 p_process_status := jai_constants.unexpected_error;
1234 p_process_message := 'Error in jai_po_osp_pkg.create_rcv_57f4. Stmt:'||lv_statement_id
1235 ||', MSG:'||SQLERRM;
1236
1237 END create_rcv_57f4;
1238 /*------------------------------------------------------------------------------------------------------------*/
1239 PROCEDURE update_57f4_on_receiving
1240 (
1241 p_shipment_header_id NUMBER,
1242 p_shipment_line_id NUMBER,
1243 p_to_organization_id NUMBER,
1244 p_ship_to_location_id NUMBER,
1245 p_item_id NUMBER,
1246 p_tran_type RCV_TRANSACTIONS.transaction_type%TYPE,
1247 p_rcv_tran_qty RCV_TRANSACTIONS.quantity%TYPE,
1248 p_new_primary_unit_of_measure RCV_SHIPMENT_LINES.primary_unit_of_measure%TYPE,
1249 p_old_primary_unit_of_measure RCV_SHIPMENT_LINES.primary_unit_of_measure%TYPE,
1250 p_unit_of_measure RCV_SHIPMENT_LINES.unit_of_measure%TYPE,
1251 p_po_header_id NUMBER,
1252 p_po_release_id NUMBER,
1253 p_po_line_id NUMBER,
1254 p_po_line_location_id NUMBER,
1255 p_last_updated_by NUMBER,
1256 p_last_update_login NUMBER,
1257 p_creation_date DATE
1258 )
1259 IS
1260
1261 v_debug BOOLEAN; --File.Sql.35 Cbabu := false;
1262 v_myfilehandle UTL_FILE.FILE_TYPE;
1263 v_utl_file_name VARCHAR2(100); --File.Sql.35 Cbabu := 'update_57f4_on_receiving.log';
1264 v_utl_location VARCHAR2(512);
1265 v_po_uom po_line_locations_all.unit_meas_lookup_code%type;
1266
1267 CURSOR c_po_uom is
1268 select unit_meas_lookup_code
1269 from po_line_locations_all
1270 where line_location_id = p_po_line_location_id;
1271
1272 CURSOR c_po_line_uom is
1273 select unit_meas_lookup_code
1274 from po_lines_all
1275 where po_line_id = p_po_line_id;
1276
1277
1278 --**********TO GET COUNT OF EXISTING 57F4'S**********
1279 CURSOR c_count_primary_57f4 IS
1280 SELECT count(1)
1281 FROM JAI_PO_OSP_HDRS
1282 WHERE
1283 -- nvl(receipt_approved,'N') != 'Y' AND - sriram - bug# 3303027
1284 cancel_flag = 'N'
1285 AND trunc(issue_date) + NVL(process_time,0) >= trunc(sysdate) -- NVL Condition added by sriram - bug# 3021456
1286 AND po_header_id = p_po_header_id
1287 AND (oth_doc_id IS NULL OR oth_doc_id = p_po_release_id)
1288 AND organization_id = p_to_organization_id
1289 AND location_id = p_ship_to_location_id
1290 -- AND (source = 'PURCHASE ORDER' OR source = 'PO RELEASE' OR source = 'RETURN TO VENDOR')
1291 AND NVL(primary_flag,'N') = 'Y'; --added on 06-jan-2000 gaurav.
1292
1293 --**********TO GET THE FORM ID**********
1294 CURSOR c_get_primary_form_id IS
1295 SELECT form_id
1296 FROM JAI_PO_OSP_HDRS
1297 WHERE
1298 --nvl(receipt_approved,'N') != 'Y' AND - sriram - bug# 3303027
1299 cancel_flag = 'N'
1300 AND trunc(issue_date) + NVL(process_time,0) >= trunc(sysdate)
1301 AND po_header_id = p_po_header_id
1302 AND (oth_doc_id IS NULL OR oth_doc_id = p_po_release_id)
1303 AND organization_id = p_to_organization_id
1304 AND location_id = p_ship_to_location_id
1305 -- AND (source = 'PURCHASE ORDER' OR source = 'PO RELEASE' OR source = 'RETURN TO VENDOR')
1306 AND NVL(primary_flag,'N') = 'Y'; --added on 06-jan-2000 gaurav.
1307
1308 --**********FETCHING VALUES FROM RCV_TRANSACTIONS**********
1309 CURSOR c_get_rcv_trans IS
1310 SELECT wip_entity_id
1311 FROM po_distributions_all
1312 WHERE line_location_id = p_po_line_location_id
1313 AND rownum = 1;
1314
1315 -- 2746952
1316 v_match_type NUMBER(1); --File.Sql.35 Cbabu := 1;
1317
1318 --**********FETCHING ALL LINES FROM JAIN57F4********
1319 -- CURSOR c_get_lines(v_primary_form_id Number, v_wip_entity_id Number) IS
1320 CURSOR c_get_lines(p_form_id Number) IS
1321 SELECT form_id, line_id, parent_item_id, item_id, bal_parent_item_qty,
1322 comp_qty_pa, despatch_qty, return_qty, item_uom, po_distribution_id
1323 -- 2746952
1324 -- , ( despatch_qty - NVL(return_qty,0)) / comp_qty_pa balance_qty
1325 , decode( v_match_type, 1, despatch_qty - NVL(return_qty,0), return_qty ) / comp_qty_pa balance_qty
1326 , despatch_qty / comp_qty_pa despatch_parent_item_qty
1327 -- , po_line_id
1328 FROM JAI_PO_OSP_LINES
1329 WHERE form_id = p_form_id
1330 -- AND bal_parent_item_qty > 0
1331 -- 2746952
1332 AND ( ( v_match_type = 1 AND ( despatch_qty - NVL(return_qty,0) ) > 0 )
1333 OR
1334 ( v_match_type = -1 AND return_qty > 0 )
1335 )
1336 AND po_line_Id = p_po_line_id
1337 AND nvl(comp_qty_pa,0) <> 0 --vkaranam for bug#4607506
1338 -- AND wip_entity_id = v_wip_entity_id
1339 -- ORDER BY form_id, po_line_id;
1340 ORDER BY form_id, line_id;
1341
1342
1343 --*********FETCHING UNIT OF MEASURE************
1344 CURSOR c_get_uom(um varchar2) IS
1345 Select unit_of_measure
1346 FROM mtl_units_of_measure
1347 WHERE uom_code = um;
1348 -- Added for bug #13541366
1349 CURSOR c_get_uom_code(um varchar2) IS
1350 Select uom_code
1351 FROM mtl_units_of_measure
1352 WHERE unit_of_measure = um;
1353
1354 /* Added for bug 14034669 *//*Start*/
1355
1356 CURSOR c_pr_form_lines(v_primary_form_id Number)
1357 IS
1358 Select item_uom, source_code
1359 from jai_po_osp_lines
1360 where form_id = v_primary_form_id
1361 and po_line_id = p_po_line_id;
1362
1363
1364
1365
1366
1367
1368 -- sriram - bug # 3021456
1369 cursor c_po_qty_cur(p_po_hdr_id number , p_po_line_id number) is
1370 select quantity_ordered
1371 from po_distributions_all
1372 where po_header_id = p_po_hdr_id
1373 and po_line_id = p_po_line_id;
1374
1375 v_item_pr_uom_code mtl_system_items.primary_uom_code%TYPE; -- eg. Ea
1376 v_item_pr_uom mtl_system_items.primary_unit_of_measure%TYPE; -- eg. Each
1377
1378 CURSOR c_item_pr_uom( p_organization_id IN NUMBER, p_inv_item_id IN NUMBER) IS
1379 SELECT primary_uom_code, primary_unit_of_measure
1380 FROM mtl_system_items
1381 WHERE organization_id = p_organization_id
1382 AND inventory_item_id = p_inv_item_id;
1383
1384 --**********VARIABLE DECLARATION**********
1385 v_count_57f4 Number;
1386 v_primary_form_id Number;
1387 v_wip_entity_id Number;
1388
1389 v_org_id Number; --File.Sql.35 Cbabu := p_to_organization_id;
1390 v_loc_id Number; --File.Sql.35 Cbabu := p_ship_to_location_id;
1391
1392 v_received_qty Number; --Added by Satya for Receipt Corrections 21/06/2001
1393 v_temp_qty Number;
1394 v_left_received_qty Number;
1395 v_bal_qty Number;
1396 v_return_qty Number; --File.Sql.35 Cbabu := 0;
1397
1398
1399 v_comp_qty_pa NUMBER;
1400 v_comp_balance_qty NUMBER;
1401 v_despatch_parent_qty NUMBER;
1402 v_form_fully_available BOOLEAN; --File.Sql.35 Cbabu := false;
1403
1404 v_po_to_rec_conv Number;
1405 v_pr_to_57f4_line_conv Number;
1406
1407 v_to_uom Varchar2(20);
1408 v_temp_rcvd_qty Number;
1409 v_left_qty Number;
1410
1411 v_tran_type Varchar2(25); --File.Sql.35 Cbabu := p_tran_type;
1412
1413 vFormId_ToBeUpdWithRegDate NUMBER;
1414
1415 v_po_qty number;
1416 v_ret_fact number;
1417 v_creation_date DATE;
1418 v_chk_uom VARCHAR2(20); -- Added for bug #13541366
1419 v_challan_uom VARCHAR2(20); -- Added for bug #13541366
1420 v_pr_form_uom VARCHAR2(25):= NULL; --Added for bug #14034669
1421 v_source_code VARCHAR2(2) := NULL; --Added for bug #14034669
1422 v_original_rcvd_qty Number := 0; --Added for bug #14034669
1423
1424
1425
1426 /* Start, Vijay Shankar for Bug#3644845 */
1427 TYPE cNumberTable IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
1428 vQtyRemaining cNumberTable;
1429 v_fun_ret_value NUMBER;
1430 v_quantity_applied NUMBER;
1431
1432 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_osp_pkg.update_57f4_on_receiving';
1433
1434 /* Bug 7028169. Added by Lakshmi Gopalsami */
1435 CURSOR c_get_item_details(cp_org_id NUMBER, cp_item_id NUMBER) IS
1436 SELECT *
1437 FROM mtl_system_items
1438 WHERE organization_id = cp_org_id
1439 AND inventory_item_id = cp_item_id;
1440
1441 r_get_po_item_details c_get_item_details%ROWTYPE;
1442 lv_item_unit_type VARCHAR2(25);
1443
1444 FUNCTION processCompBalances(pEvent IN NUMBER,
1445 pItemId IN NUMBER,
1446 pQtyToApply IN NUMBER
1447 -- Bug 7028169. Added by Lakshmi Gopalsami
1448 ,pPoQty IN NUMBER
1449 ,pItemUnitType IN VARCHAR2
1450 ) RETURN NUMBER IS
1451
1452 /* Functionality of this Function
1453 pEvent = 0, plsql table initialization event.
1454 Allowed parameter values during this event,
1455 pItemId should be PRIMARY 57F4 FORM_ID, pQtyToApply = received_quantity
1456
1457 pEvent = 1, during this event quantity has to be reduced/added for specified item. Called when 57F4 forms other than RTV
1458 is being matched(applied)
1459 Allowed parameter values during this event,
1460 pItemId = item_id being processed, pQtyToApply = quantity to be reduced/added for item
1461
1462 pEvent = 2(RTV), during this event pQtyToApply should be reduced/added for all items of plsql tablem, because this is called
1463 when 57F4 forms created by RTV is being applied
1464 Allowed parameter values during this event,
1465 pItemId = <value not considered>, pQtyToApply = quantity to be reduced/added for all items (i.e quantity applied on 57F4 form created during RTV)
1466
1467 pEvent = 3, calling procedure is expecting the qty remaining of item specified
1468 Allowed parameter/return values during this event,
1469 pItemId = item_id being processed, pQtyToApply = <value not considered>
1470 vReturnValue return quantity remaining for item
1471
1472 pEvent = 4(RTV), calling procedure is expecting the qty remaining to apply, when 57f4 form created by RTV is being applied
1473 (balance of any item can be returned)
1474 Allowed parameter/return values during this event,
1475 pItemId = <value not considered>, pQtyToApply = <value not considered>
1476 vReturnValue return quantity remaining for first item
1477
1478 pEvent = 5, calling procedure is asking whether all received quantity is appied onto 57F4 forms or not
1479 Allowed parameter values during this event,
1480 pItemId = <value not considered>, pQtyToApply = <value not considered>
1481 vRerurnValue = 0 If fully applied else -1
1482 Always
1483 vReturnValue = 0 indicates success
1484 and vReturnValue = -1 indicates Error
1485 */
1486
1487 vItemId NUMBER(15);
1488 vReturnValue NUMBER;
1489 vProQty NUMBER;
1490 BEGIN
1491
1492
1493 IF pEvent = 0 THEN
1494 FOR ii IN /* Bug 7028169. Added by Lakshmi Gopalsami
1495 * selected all the values
1496 */
1497 (SELECT *
1498 FROM JAI_PO_OSP_LINES WHERE form_id = pItemId AND po_line_id = p_po_line_id) LOOP
1499 /* Bug 7028169. Added by Lakshmi Gopalsami
1500 * Added logic for initializing the pl/sql table for
1501 * resource unit type
1502 * We need to proportionate the receipt quantity
1503 * with that of PO qty and 57F4 despatch qty
1504 */
1505 IF r_get_po_item_details.outside_operation_uom_type ='RESOURCE'
1506 AND ii.source_code ='W' THEN
1507 vProQty := (ii.original_qty/ pPoQty) * pQtyToApply;
1508 vQtyRemaining(ii.item_id) := vProQty;
1509
1510
1511 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','12.1 vQtyRemaining(ii.item_id) :'||vQtyRemaining(ii.item_id)||' ii.original_qty :'
1512 ||ii.original_qty||' pPoQty :'||pPoQty||' pQtyToApply :'||pQtyToApply||' vProQty :'||vProQty||' pItemId :'||pItemId);
1513 ELSE
1514 vQtyRemaining(ii.item_id) := pQtyToApply;
1515 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','12.2 vQtyRemaining(ii.item_id)'||vQtyRemaining(ii.item_id) );
1516
1517 END IF ;
1518
1519 END LOOP;
1520
1521 ELSIF pEvent = 1 THEN
1522 vQtyRemaining(pItemId) := vQtyRemaining(pItemId) - pQtyToApply;
1523 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','30 vQtyRemaining(pItemId) '||vQtyRemaining(pItemId));
1524
1525 ELSIF pEvent = 2 THEN
1526 vItemId := vQtyRemaining.FIRST;
1527 WHILE vItemId IS NOT NULL LOOP
1528 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','28.1 vItemId :'||vItemId||' vQtyRemaining(vItemId) :'||vQtyRemaining(vItemId)||' pQtyToApply :'||pQtyToApply);
1529 vQtyRemaining(vItemId) := vQtyRemaining(vItemId) - pQtyToApply;
1530 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','28.2 vQtyRemaining(vItemId) :'||vQtyRemaining(vItemId)||' vItemId :'||vItemId);
1531 vItemId := vQtyRemaining.NEXT(vItemId);
1532 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','28.3 vItemId :'||vItemId);
1533 END LOOP;
1534 --jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','28.3 vQtyRemaining(vItemId) '||vQtyRemaining(vItemId));
1535
1536 ELSIF pEvent = 3 THEN
1537 vReturnValue := vQtyRemaining(pItemId);
1538 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','13 vReturnValue'||vReturnValue );
1539
1540
1541 ELSIF pEvent = 4 THEN
1542 vItemId := vQtyRemaining.FIRST;
1543 IF vItemId IS NOT NULL THEN
1544 vReturnValue := vQtyRemaining(vItemId);
1545 ELSE
1546 vReturnValue := 0;
1547 END IF;
1548
1549 ELSIF pEvent = 5 THEN
1550 vItemId := vQtyRemaining.FIRST;
1551 WHILE vItemId IS NOT NULL LOOP
1552 IF vQtyRemaining(vItemId) <> 0 THEN
1553 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','35.1 vQtyRemaining(vItemId) :'||vQtyRemaining(vItemId)||' vItemId :'||vItemId);
1554 vReturnValue := -1;
1555 exit;
1556 else
1557 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','35.2 vQtyRemaining(vItemId) :'||vQtyRemaining(vItemId)||' vItemId :'||vItemId);
1558 vReturnValue := 0;
1559 END IF;
1560 vItemId := vQtyRemaining.NEXT(vItemId);
1561 END LOOP;
1562
1563 END IF;
1564
1565 RETURN vReturnValue;
1566
1567
1568 END processCompBalances;
1569 /* End, Vijay Shankar for Bug#3644845 */
1570
1571 BEGIN
1572
1573 /*------------------------------------------------------------------------------------------
1574 CHANGE HISTORY: FILENAME: ja_in_update_57F4.sql
1575 S.No Date Author and Details
1576 ------------------------------------------------------------------------------------------
1577 1 01-JUN-2001 Satya Added DUAL UOM functionality
1578
1579 2 29-OCT-2002 Nagaraj.s for Bug2643016
1580 As Functionally required, an Update statement is written to update the CR_REG_ENTRY_DATE of
1581 the ja_in_57f4_table. This will definitely have implications on Approve 57f4 receipt screen on
1582 Modvat claim but since no Modvat claim is available for 57f4 register, this has been approved
1583 functionally.
1584 3 22-JAN-2003 cbabu for Bug#2746952, FileVersion# 615.2
1585 During the RETURN TO VENDOR transaction for the shipment line, the code is getting executed and
1586 return quantity is getting updated. This is happening when a partial receipt is made and then RTV
1587 is made for the same
1588 4. 08-JAN-2004 ssumaith - bug# 3303027 File Version # 618.1
1589
1590 When the primary form is receipt_approved = 'Y' , when a receipt is made , return_quantity
1591 field does not get updated for the RTV OSP form . There is a check in this procedure which
1592 is preventing the entry of control into the code paths which update the RTV form.
1593
1594 5. 01-mar-2004 ssumaith - bug# 3446045 file version 618.2.
1595
1596 unit_meas_lookup_code column in the po_line_locations_all table is null in the clients
1597 instance . The value in this field is being used as a basis of uom comparison. This
1598 is causing wrong uom conversion and return quantuty is not getting updated correctly
1599 when uom is changed.
1600
1601 This has been corrected by using the unit_meas_lookup_code of the po_lines_all table
1602 in case the value retreived from the po_line_locations_all table is null.
1603
1604 6 03-JUN-2004 Vijay Shankar for Bug# 3644845, Version:115.1
1605 return quantity is not getting updated when one of the OSP component is sent through Secondary form because the
1606 code assumes that the components related to n OSP items will be sent in one form. Now the code is modified by
1607 adding an internal function processCompBalances and calling it from code during following events
1608 1) before main processing loop for initialization of comp balances to be updated on 57f4 lines
1609 2) after every form, whether any balances for components are left
1610 3) start of each line being processed, for remaining quantity to be applied on 57f4 line
1611 4) end of lines loop to update plsql table with quantity applied onto the line
1612 New function is written as a central code which manages the component quantities remaining to be applied onto 57f4 lines
1613 --------------------------------------------------------------------------------------------*/
1614
1615 /*
1616 --File.Sql.35 Cbabu
1617 v_utl_file_name := 'update_57f4_on_receiving.log';
1618
1619 IF v_debug THEN
1620 BEGIN
1621
1622 SELECT DECODE(SUBSTR (value,1,INSTR(value,',') -1),NULL, Value,SUBSTR (value,1,INSTR(value,',') -1))
1623 INTO v_utl_location
1624 FROM v$parameter WHERE name = 'utl_file_dir';
1625
1626 v_myfilehandle := UTL_FILE.FOPEN(v_utl_location, v_utl_file_name, 'A');
1627
1628 EXCEPTION
1629 WHEN OTHERS THEN
1630 v_debug := FALSE;
1631 END;
1632 END IF;
1633 */
1634
1635 --File.Sql.35 Cbabu
1636 v_debug := false;
1637 v_match_type := 1;
1638 v_org_id := p_to_organization_id;
1639 v_loc_id := p_ship_to_location_id;
1640 v_return_qty := 0;
1641 v_form_fully_available := false;
1642 v_tran_type := p_tran_type;
1643 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','1 update receiving quantity ');
1644
1645 IF v_debug THEN
1646 jai_cmn_utils_pkg.print_log(v_utl_file_name,'*******START Time Stamp*******' ||TO_CHAR(SYSDATE,'DD/MM/RRRR HH24:MI:SS')
1647 ||' Transaction Type is ' || v_tran_type
1648 );
1649 END IF;
1650
1651 v_received_qty := p_rcv_tran_qty;
1652 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','2 v_received_qty '|| v_received_qty);
1653
1654 -- 2746952
1655 IF p_rcv_tran_qty >= 0 THEN
1656 v_match_type := 1;
1657 ELSE
1658 v_match_type := -1;
1659 END IF;
1660
1661 OPEN c_get_rcv_trans;
1662 FETCH c_get_rcv_trans INTO v_wip_entity_id;
1663 CLOSE c_get_rcv_trans;
1664
1665 IF v_debug THEN
1666 jai_cmn_utils_pkg.print_log(v_utl_file_name,'1 line_location_id -> '|| p_po_line_location_id
1667 ||', v_wip_entity_id -> '|| v_wip_entity_id
1668 );
1669 END IF;
1670
1671 OPEN c_count_primary_57f4;
1672 FETCH c_count_primary_57f4 INTO v_count_57f4;
1673 CLOSE c_count_primary_57f4;
1674 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','3 v_count_57f4 '|| v_count_57f4);
1675
1676 IF v_debug THEN
1677 jai_cmn_utils_pkg.print_log(v_utl_file_name,'2 po_header_id -> '|| p_po_header_id
1678 ||', po_release_id -> '|| p_po_release_id ||', to_organization_id -> '|| p_to_organization_id
1679 ||', ship_to_location_id -> '|| p_ship_to_location_id ||', v_count_57f4 -> '|| v_count_57f4
1680 ||', shipment_header_id -> '|| p_shipment_header_id ||', shipment_line_id -> '|| p_shipment_line_id
1681 ||', v_tran_type -> '|| v_tran_type ||', v_received_qty -> '|| v_received_qty
1682 ||', po_line_id -> '|| p_po_line_id
1683 );
1684 END IF;
1685
1686 /* Bug 7028169. Added by Lakshmi Gopalsami
1687 * Get the details of unit_type of PO OSP item.
1688 */
1689 OPEN c_get_item_details(p_to_organization_id,p_item_id);
1690 FETCH c_get_item_details INTO r_get_po_item_details;
1691 CLOSE c_get_item_details;
1692
1693 IF (v_match_type = 1 AND v_received_qty > 0) OR (v_match_type = -1 AND v_received_qty < 0) THEN
1694
1695 IF v_count_57f4 = 1 THEN
1696
1697 OPEN c_get_primary_form_id;
1698 FETCH c_get_primary_form_id INTO v_primary_form_id;
1699 CLOSE c_get_primary_form_id;
1700
1701 open c_po_qty_cur(p_po_header_id , p_po_line_id);
1702 fetch c_po_qty_cur into v_po_qty;
1703 close c_po_qty_cur;
1704 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','4 v_po_qty '|| v_po_qty );
1705
1706 IF v_debug THEN
1707 jai_cmn_utils_pkg.print_log(v_utl_file_name,'3 v_primary_form_id -> '|| v_primary_form_id
1708 ||', v_received_qty -> '|| v_received_qty
1709 ||', v_po_qty -> '|| v_po_qty
1710 );
1711 END IF;
1712
1713 open c_po_uom;
1714 fetch c_po_uom into v_po_uom;
1715 close c_po_uom;
1716 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','5 v_po_uom '|| v_po_uom );
1717
1718 IF v_debug THEN
1719 jai_cmn_utils_pkg.print_log(v_utl_file_name,'4.122 v_po_uom -> '|| v_po_uom);
1720 END IF;
1721
1722 -- added for bug#3446045 - sriram
1723 if v_po_uom is null then
1724 open c_po_line_uom;
1725 fetch c_po_line_uom into v_po_uom;
1726 close c_po_line_uom;
1727 end if;
1728
1729 -- ends here additions for bug# 3446045
1730
1731 IF v_debug THEN
1732 jai_cmn_utils_pkg.print_log(v_utl_file_name,'4.123 v_po_uom -> '|| v_po_uom);
1733 END IF;
1734 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','5.1 v_po_uom '|| v_po_uom );
1735
1736 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','5.2 p_unit_of_measure '|| p_unit_of_measure);
1737
1738 -- Determines the conversion factor between PO and RECEIPT uom's if they are different
1739 if v_po_uom <> p_unit_of_measure then
1740
1741 v_po_to_rec_conv := INV_CONVERT.INV_UM_CONVERT
1742 (p_item_id, NULL, NULL, NULL, NULL,
1743 p_unit_of_measure, v_po_uom);
1744
1745 IF v_debug THEN
1746 jai_cmn_utils_pkg.print_log(v_utl_file_name,'4.124 v_po_uom -> '|| v_po_uom
1747 ||', p_unit_of_measure -> '|| p_unit_of_measure
1748 ||', v_po_to_rec_conv -> '|| nvl(v_po_to_rec_conv, -1275)
1749 );
1750 END IF;
1751
1752 IF v_po_to_rec_conv < 0 OR v_po_to_rec_conv IS NULL THEN
1753 v_po_to_rec_conv := 1;
1754 END IF;
1755
1756 ELSE
1757 v_po_to_rec_conv := 1;
1758 end if;
1759 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','7 v_po_to_rec_conv '|| v_po_to_rec_conv );
1760
1761 -- this takes care if there is UOM Change beteen PO and RECEIPT
1762 v_received_qty := v_received_qty * v_po_to_rec_conv;
1763
1764 v_left_received_qty := v_received_qty;
1765 v_original_rcvd_qty := v_received_qty;
1766
1767 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','8 v_received_qty '|| v_received_qty||' v_original_rcvd_qty :'||v_original_rcvd_qty );
1768
1769 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','9 v_left_received_qty '|| v_left_received_qty );
1770
1771 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','10 v_primary_form_id '|| v_primary_form_id );
1772
1773 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','11 outside_operation_uom_type '|| r_get_po_item_details.outside_operation_uom_type );
1774 -- v_fun_ret_value :=
1775 -- Initialize PLSQL table, Vijay Shankar for Bug# 3644845
1776 v_fun_ret_value := processCompBalances(0,
1777 v_primary_form_id, v_received_qty
1778 -- Bug 7028169. Added by Lakshmi Gopalsami
1779 ,v_po_qty
1780 ,r_get_po_item_details.outside_operation_uom_type);
1781 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','14 v_fun_ret_value '|| v_fun_ret_value );
1782
1783
1784 FOR each_form IN
1785 (
1786 SELECT '1' seq, form_id, source, form_id*v_match_type order_by
1787 FROM JAI_PO_OSP_HDRS
1788 WHERE form_id = v_primary_form_id
1789
1790 UNION
1791
1792 SELECT '2' seq, form_id, source, form_id*v_match_type order_by
1793 FROM JAI_PO_OSP_HDRS
1794 WHERE primary_form_id = v_primary_form_id
1795 AND NVL(receipt_approved,'N') <> 'Y'
1796 AND issue_approved = 'Y'
1797 ORDER BY order_by
1798
1799 )
1800 LOOP
1801
1802 vFormId_ToBeUpdWithRegDate := null;
1803
1804 FOR each_line in c_get_lines(each_form.form_id) LOOP
1805 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','15 Inside each line loop '||each_form.form_id||' each_line.item_id :'||each_line.item_id);
1806
1807 v_ret_fact := 1;
1808 /* Added for bug 14034669 *//*Start*/
1809
1810 OPEN c_pr_form_lines(v_primary_form_id);
1811 FETCH c_pr_form_lines INTO v_pr_form_uom, v_source_code;
1812 CLOSE c_pr_form_lines;
1813
1814 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','16.2 v_pr_form_uom ' ||v_pr_form_uom||' each_line.item_uom : '||each_line.item_uom);
1815
1816 v_pr_to_57f4_line_conv := 1;
1817
1818 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','16.3 v_pr_to_57f4_line_conv ' ||v_pr_to_57f4_line_conv);
1819
1820 IF v_pr_form_uom <> each_line.item_uom AND r_get_po_item_details.outside_operation_uom_type = 'RESOURCE'
1821 AND v_source_code ='W'
1822 THEN
1823 /* v_pr_to_57f4_line_conv := INV_CONVERT.INV_UM_CONVERT
1824 (each_line.item_id, NULL, NULL, v_pr_form_uom, each_line.item_uom, -- Modified for bug #13541366
1825 null, null);
1826
1827 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','16.4 v_pr_to_57f4_line_conv : ' ||v_pr_to_57f4_line_conv);
1828
1829 v_received_qty := v_received_qty * v_pr_to_57f4_line_conv; */
1830
1831 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','16.5 each_line.form_id : ' || each_line.form_id||' v_original_rcvd_qty :'||v_original_rcvd_qty);
1832
1833 v_fun_ret_value := processCompBalances(0,
1834 each_line.form_id,
1835 v_original_rcvd_qty
1836 ,v_po_qty
1837 ,r_get_po_item_details.outside_operation_uom_type);
1838
1839 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','16.6 v_fun_ret_value '|| v_fun_ret_value );
1840
1841 END IF;
1842
1843 v_pr_to_57f4_line_conv := 1;
1844
1845 /* Added for bug 14034669 *//*End*/
1846
1847 --Start, Vijay Shankar for Bug# 3644845
1848 IF each_form.source = 'RETURN TO VENDOR' THEN
1849 v_received_qty := processCompBalances(4, null, null
1850 -- Bug 7028169. Added by Lakshmi Gopalsami
1851 ,v_po_qty
1852 ,r_get_po_item_details.outside_operation_uom_type);
1853 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','16 For RTV v_received_qty ' ||v_received_qty);
1854
1855
1856 ELSE
1857 v_received_qty := processCompBalances(3, each_line.item_id,null
1858 -- Bug 7028169. Added by Lakshmi Gopalsami
1859 ,v_po_qty
1860 ,r_get_po_item_details.outside_operation_uom_type);
1861 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','16.1 v_received_qty ' ||v_received_qty);
1862
1863 END IF;
1864 --End, Vijay Shankar for Bug# 3644845
1865
1866 OPEN c_item_pr_uom(p_to_organization_id, each_line.item_id);
1867 FETCH c_item_pr_uom INTO v_item_pr_uom_code, v_item_pr_uom;
1868 CLOSE c_item_pr_uom;
1869
1870 /* Added for bug #13541366 *//*Start*/
1871 v_chk_uom := null;
1872 v_challan_uom := null;
1873
1874 OPEN c_get_uom_code(p_unit_of_measure);
1875 FETCH c_get_uom_code INTO v_chk_uom;
1876 CLOSE c_get_uom_code;
1877
1878 IF each_line.item_uom <> v_item_pr_uom_code AND each_line.item_uom = v_chk_uom
1879 THEN
1880
1881 v_challan_uom := v_chk_uom;
1882
1883 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','17.2 v_chk_uom ' ||v_chk_uom||' v_challan_uom :'||v_challan_uom);
1884
1885 ELSIF each_line.item_uom = v_item_pr_uom_code AND each_line.item_uom <> v_chk_uom
1886 THEN
1887
1888 v_challan_uom := v_chk_uom;
1889
1890 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','17.3 v_chk_uom ' ||v_chk_uom||' v_challan_uom :'||v_challan_uom);
1891 ELSE
1892
1893 v_challan_uom := v_item_pr_uom_code;
1894
1895 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','17.4 v_chk_uom ' ||v_chk_uom||' v_challan_uom :'||v_challan_uom);
1896
1897 END IF;
1898
1899 /* Added for bug #13541366 *//*End*/
1900 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','18 v_item_pr_uom_code ' ||v_item_pr_uom_code||' v_item_pr_uom '||v_item_pr_uom||' each_line.item_uom'||each_line.item_uom||' v_chk_uom :'||v_chk_uom);
1901
1902 -- Determines the conversion factor between Component Primary UOM and 57F4 Line uom's if they are different
1903 if v_challan_uom <> each_line.item_uom then -- Modified for bug #13541366
1904
1905 v_pr_to_57f4_line_conv := INV_CONVERT.INV_UM_CONVERT
1906 (each_line.item_id, NULL, NULL, v_challan_uom, each_line.item_uom, -- Modified for bug #13541366
1907 null, null);
1908 -- p_unit_of_measure, v_to_uom);
1909
1910 IF v_debug THEN
1911 jai_cmn_utils_pkg.print_log(v_utl_file_name,'4.1x2 v_po_uom -> '|| v_po_uom
1912 ||', v_to_uom -> '|| v_to_uom
1913 ||', v_pr_to_57f4_line_conv -> '|| nvl(v_pr_to_57f4_line_conv, -1275)
1914 );
1915 END IF;
1916
1917 IF v_pr_to_57f4_line_conv < 0 OR v_pr_to_57f4_line_conv IS NULL THEN
1918 v_pr_to_57f4_line_conv := 1;
1919 END IF;
1920
1921 ELSE
1922 v_pr_to_57f4_line_conv := 1;
1923 end if;
1924 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','21 v_pr_to_57f4_line_conv ' ||v_pr_to_57f4_line_conv);
1925
1926 v_comp_qty_pa := each_line.comp_qty_pa * v_pr_to_57f4_line_conv;
1927 v_despatch_parent_qty := each_line.despatch_qty / v_comp_qty_pa ;
1928 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','21.1 v_comp_qty_pa ' ||v_comp_qty_pa||' v_despatch_parent_qty '||v_despatch_parent_qty);
1929
1930 --converting the dipatch qty to return qty uom
1931 IF v_match_type = 1 THEN
1932 v_comp_balance_qty := (each_line.despatch_qty - NVL(each_line.return_qty,0)) / v_comp_qty_pa ;
1933 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','21.2 v_comp_balance_qty ' ||v_comp_balance_qty);
1934 ELSE
1935 v_comp_balance_qty := NVL(each_line.return_qty, 0) / v_comp_qty_pa ;
1936 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','21.3 v_comp_balance_qty ' ||v_comp_balance_qty);
1937 END IF;
1938
1939 IF v_debug THEN
1940 jai_cmn_utils_pkg.print_log(v_utl_file_name,'4 form_id -> '|| each_line.form_id
1941 ||', line_id -> '|| each_line.line_id ||', parent_item_id -> '|| each_line.parent_item_id
1942 ||', item_id -> '|| each_line.item_id ||', bal_parent_item_qty -> '|| each_line.bal_parent_item_qty
1943 ||', comp_qty_pa -> '|| each_line.comp_qty_pa ||', despatch_qty -> '|| each_line.despatch_qty
1944 ||', return_qty -> '|| each_line.return_qty ||', item_uom -> '|| each_line.item_uom
1945 );
1946 jai_cmn_utils_pkg.print_log(v_utl_file_name,'4.1.1 unit_of_measure -> '|| p_unit_of_measure
1947 ||', primary_unit_of_measure -> '|| p_new_primary_unit_of_measure ||', item_id -> '|| p_item_id
1948 ||', v_temp_rcvd_qty -> '|| v_temp_rcvd_qty ||', v_received_qty -> '|| v_received_qty
1949 ||', bal_qty -> '|| each_line.balance_qty ||', des_par_itm_qty -> '|| each_line.despatch_parent_item_qty
1950 ||', bal_par_itm_qty -> '|| each_line.bal_parent_item_qty ||', v_match_type -> '|| v_match_type
1951
1952 );
1953 jai_cmn_utils_pkg.print_log(v_utl_file_name,'4.4 v_comp_qty_pa -> '|| v_comp_qty_pa
1954 ||', v_despatch_parent_qty -> '|| v_despatch_parent_qty
1955 ||', v_comp_balance_qty -> '|| v_comp_balance_qty
1956 ||', v_pr_to_57f4_line_conv -> '|| v_pr_to_57f4_line_conv
1957 );
1958 END IF;
1959
1960 ----after converting the dipatch qty to return qty uom value assigning it to balance qty
1961
1962 v_received_qty := v_received_qty / v_comp_qty_pa ; --Added for bug #13541366
1963
1964
1965
1966
1967
1968
1969 v_bal_qty := v_comp_balance_qty;
1970 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','21.4 v_received_qty ' ||v_received_qty||' v_bal_qty : '||v_bal_qty);
1971
1972 IF v_match_type = 1 THEN -- this handles +ve quantity RECEIVE / CORRECTed through RCV_TRANSACTIONS base form
1973
1974 IF v_bal_qty >= v_received_qty THEN
1975 --temp qty which is assigned to return qty value is assigned with the return qty value after converion to return qty uom
1976 v_temp_qty := v_received_qty * v_comp_qty_pa;
1977 v_left_received_qty := 0;
1978 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','22 v_received_qty ' ||v_received_qty||' v_comp_qty_pa '||v_comp_qty_pa||' v_temp_qty '|| v_temp_qty||' v_left_received_qty '||v_left_received_qty);
1979
1980 v_quantity_applied := v_received_qty * v_comp_qty_pa; --Added * v_comp_qty_pa for bug #13541366 -- Vijay Shankar for Bug# 3644845, full received quantity applied
1981 v_bal_qty := v_bal_qty - v_received_qty;
1982
1983 ELSE
1984 v_temp_qty := v_bal_qty * v_comp_qty_pa;
1985 v_left_received_qty := v_received_qty - v_bal_qty;
1986 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','23 v_bal_qty ' ||v_bal_qty||' v_comp_qty_pa '||v_comp_qty_pa||' v_temp_qty '|| v_temp_qty||' v_left_received_qty '||v_left_received_qty);
1987
1988 v_quantity_applied := v_bal_qty * v_comp_qty_pa; --Added * v_comp_qty_pa for bug #13541366 -- Vijay Shankar for Bug# 3644845, quantity on 57F4 line is Complete. Should update next 57F4 line with remaining qty
1989 v_bal_qty := 0;
1990
1991 END IF;
1992
1993 -- 2746952
1994 -- this handles -ve quantity CORRECTed through RCV_TRANSACTIONS base form
1995 ELSIF v_match_type = -1 THEN
1996
1997 -- v_bal_qty is +ve and v_received_qty is -ve if code enters this path
1998 -- v_bal_qty is assigned with return_qty in this case
1999
2000 IF v_bal_qty > abs(v_received_qty) THEN
2001 v_temp_qty := v_received_qty * v_comp_qty_pa;
2002 v_left_received_qty := 0;
2003 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','24 v_received_qty ' ||v_received_qty||' v_comp_qty_pa '||v_comp_qty_pa||' v_temp_qty '|| v_temp_qty);
2004
2005 v_quantity_applied := v_received_qty * v_comp_qty_pa; --Added * v_comp_qty_pa for bug #13541366 -- Vijay Shankar for Bug# 3644845, full received quantity applied
2006 v_bal_qty := v_bal_qty + v_received_qty;
2007 ELSE
2008 v_temp_qty := (v_bal_qty * v_match_type) * v_comp_qty_pa;
2009 v_left_received_qty := v_received_qty + v_bal_qty;
2010 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','25 v_bal_qty ' ||v_bal_qty||' v_comp_qty_pa '||v_comp_qty_pa||' v_temp_qty '|| v_temp_qty);
2011
2012 v_quantity_applied := -v_bal_qty * v_comp_qty_pa; --Added * v_comp_qty_pa for bug #13541366 -- Vijay Shankar for Bug# 3644845, quantity on 57F4 line is Complete. Should update next 57F4 line with remaining qty
2013 v_bal_qty := v_despatch_parent_qty;
2014 END IF;
2015
2016 END IF;
2017
2018 -- this will be useful incase of only -ve quantity CORRECTion's
2019 IF v_bal_qty = v_despatch_parent_qty THEN
2020 v_form_fully_available := true;
2021 END IF;
2022
2023 v_return_qty := v_temp_qty;
2024 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','26 v_return_qty ' ||v_return_qty);
2025
2026 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','27 v_bal_qty -> '|| v_bal_qty
2027 ||', v_return_qty -> '|| v_temp_qty||', v_left_received_qty -> '
2028 || v_left_received_qty||',each_line.line_id -> '||each_line.line_id
2029 );
2030 --------
2031
2032 IF v_debug THEN
2033 jai_cmn_utils_pkg.print_log(v_utl_file_name,'5.1 v_bal_qty -> '|| v_bal_qty
2034 ||', v_return_qty -> '|| v_temp_qty||', v_left_received_qty -> '|| v_left_received_qty
2035 );
2036 END IF;
2037
2038 UPDATE JAI_PO_OSP_LINES
2039 SET return_qty = round((nvl(return_qty,0) + nvl(v_ret_fact,1) * v_return_qty), 5),
2040 bal_parent_item_qty = v_bal_qty,
2041 last_update_date = sysdate,
2042 last_updated_by = p_last_updated_by,
2043 last_update_login = p_last_update_login
2044 where line_id = each_line.line_id;
2045
2046 --Start, Vijay Shankar for Bug# 3644845
2047 IF each_form.source = 'RETURN TO VENDOR' THEN
2048 v_fun_ret_value := processCompBalances(2, null, nvl(v_quantity_applied,0)
2049 -- Bug 7028169. Added by Lakshmi Gopalsami
2050 ,v_po_qty
2051 ,r_get_po_item_details.outside_operation_uom_type);
2052 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','29 v_fun_ret_value ' ||v_fun_ret_value||' v_quantity_applied '||v_quantity_applied);
2053 ELSE
2054 v_fun_ret_value := processCompBalances(1, each_line.item_id, nvl(v_quantity_applied,0)
2055 -- Bug 7028169. Added by Lakshmi Gopalsami
2056 ,v_po_qty
2057 ,r_get_po_item_details.outside_operation_uom_type);
2058 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','31 v_fun_ret_value ' ||v_fun_ret_value||' v_quantity_applied '||v_quantity_applied);
2059 END IF;
2060 --End, Vijay Shankar for Bug# 3644845
2061
2062 IF v_debug THEN
2063 jai_cmn_utils_pkg.print_log(v_utl_file_name,'5.3 updatedCount -> '|| SQL%ROWCOUNT
2064 ||', v_left_received_qty -> '|| v_left_received_qty
2065 );
2066 END IF;
2067
2068 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','31 v_quantity_applied: '||v_quantity_applied);
2069
2070 v_return_qty := 0;
2071 v_comp_qty_pa := null;
2072 v_despatch_parent_qty := null;
2073 v_comp_balance_qty := null;
2074 v_quantity_applied := null;
2075 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','32 v_left_received_qty: ' || v_left_received_qty||' v_quantity_applied: '||v_quantity_applied);
2076
2077 vFormId_ToBeUpdWithRegDate := each_form.form_id;
2078 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','33 End of line loop');
2079
2080 END LOOP;
2081
2082 -- following if condition will get satisfied when balance is available on form which got
2083 -- updated with this RECEIPT/CORRECT transaction
2084 IF vFormId_ToBeUpdWithRegDate IS NOT NULL THEN
2085 IF v_form_fully_available THEN
2086 v_creation_date := null;
2087 ELSE
2088 v_creation_date := p_creation_date;
2089 END IF;
2090
2091 UPDATE JAI_PO_OSP_HDRS
2092 SET CR_REG_ENTRY_DATE = v_creation_date
2093 WHERE form_id = vFormId_ToBeUpdWithRegDate;
2094
2095 END IF;
2096 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','34 After updating osp hdrs with date ');
2097
2098 -- If condition added by Vijay Shankar for Bug# 3644845
2099 IF processCompBalances(5, null, null
2100 -- Bug 7028169. Added by Lakshmi Gopalsami
2101 ,v_po_qty
2102 ,r_get_po_item_details.outside_operation_uom_type) = 0 THEN
2103 -- control comes here if all the received quantity is applied onto 57F4 forms
2104 jai_cmn_utils_pkg.write_fnd_log_msg('b13541366.log','36 Remaining qty is 0, comp balances 5 ');
2105 EXIT;
2106 END IF;
2107
2108 /* Vijay Shankar for Bug# 3644845
2109 EXIT WHEN v_left_received_qty = 0;
2110 v_received_qty := v_left_received_qty;
2111 */
2112 v_form_fully_available := false;
2113
2114 END LOOP;
2115
2116 END IF;
2117
2118 END IF;
2119
2120 IF v_debug THEN
2121 UTL_FILE.fclose(v_myfilehandle);
2122 END IF;
2123
2124 EXCEPTION
2125 WHEN OTHERS THEN
2126 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2127 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
2128 app_exception.raise_exception;
2129
2130 END update_57f4_on_receiving;
2131
2132 END jai_po_osp_pkg;