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