[Home] [Help]
216:
217: l_org_name HR_ALL_ORGANIZATION_UNITS_VL.name%TYPE;
218: l_msg_text fnd_new_messages.message_text%TYPE;
219:
220: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
221: l_ret_status VARCHAR2(1);
222: l_msg_count NUMBER;
223: l_msg_data VARCHAR2(100);
224: l_style_item_id NUMBER;
280: err_text);
281: uom_default := substr(uom_default_tmp,1,25);
282:
283: IF l_inv_debug_level IN(101, 102) THEN
284: INVPUTLI.info('INVPAGI2: uom default is '|| uom_default);
285: END IF;
286:
287:
288: if rtn_status <> 0 and
362:
363: IF default_flag = 2 THEN --Sequence generated item nos only in pre-defaulting phase
364:
365: IF l_inv_debug_level IN(101, 102) THEN
366: INVPUTLI.info('INVPAGI2: About to handle sequence generated item number case');
367: END IF;
368:
369: SELECT DISTINCT item_catalog_group_id BULK COLLECT INTO catalog_table
370: FROM mtl_system_items_interface
392: FROM mtl_parameters)
393: AND (msii.item_catalog_group_id IS NOT NULL AND msii.item_catalog_group_id = catalog_table(i));
394:
395: IF l_inv_debug_level IN(101, 102) THEN
396: INVPUTLI.info('INVPAGI2: Identified rows for SEQ generation are:' || SQL%ROWCOUNT);
397: END IF;
398:
399: l_seq_exists := 1;
400: END IF;
436: -- End of Bug 5118572
437: END IF; --Seq gen Item Nos only in pre defaulting phase
438:
439: IF l_inv_debug_level IN(101, 102) THEN
440: INVPUTLI.info('INVPAGI2: About to handle SKU items');
441: END IF;
442:
443: /* Mark all SKU items with no style item to error */
444: UPDATE mtl_system_items_interface msii
527: END IF;
528: END IF;
529:
530: IF l_inv_debug_level IN(101, 102) THEN
531: INVPUTLI.info('INVPAGI2: About to enter DSQL block');
532: END IF;
533:
534:
535: BEGIN /* PL-SQL Block for doing the dynamic SQL part*/
536:
537: ff_statement_temp := NULL;
538: ff_err_temp := NULL;
539: DSQL_ff_c := dbms_sql.open_cursor;
540: dummy_ret_code := INVPUTLI.get_dynamic_sql_str(1, ff_statement_temp, ff_err_temp);
541:
542: /* Now append the sql statement to the generated dynamic sql where clause
543: ** NP 02MAY96 Added xset_id and a i
544: ** statement to BIND the set_id variable to DSQL_ff_c */
560:
561: DSQL_ff_rows_processed := dbms_sql.execute(DSQL_ff_c);
562:
563: IF l_inv_debug_level IN(101, 102) THEN
564: INVPUTLI.info('INVPAGI2: About to enter DSQL loop');
565: END IF;
566:
567: loop
568: if dbms_sql.fetch_rows(DSQL_ff_c) > 0 then
598: if dbms_sql.is_open(DSQL_ff_c) then
599: dbms_sql.close_cursor(DSQL_ff_c);
600: end if;
601: IF l_inv_debug_level IN(101, 102) THEN
602: INVPUTLI.info('INVPAGI2:out of loop ');
603: END IF;
604:
605: EXCEPTION
606: when others then
635: and msi.organization_id = mp.organization_id + 0
636: and ' || ff_statement_temp;
637:
638: IF l_inv_debug_level IN(101, 102) THEN
639: INVPUTLI.info('INVPAGI2: About to enter header cursorloop');
640: END IF;
641:
642:
643: update MTL_ITEM_REVISIONS_INTERFACE i
671: --End : Performance enhancements
672:
673: FOR cr in header loop
674: IF l_inv_debug_level IN(101, 102) THEN
675: INVPUTLI.info('INVPAGI2: Set_id for current_row is '||cr.set_process_id);
676: END IF;
677: rtn_status := 0;
678: org_flag := 0;
679: tran_id := cr.transaction_id;
921: */
922: if cr.inventory_item_id is null then
923: BEGIN
924: IF l_inv_debug_level IN(101, 102) THEN
925: INVPUTLI.info('INVPAGI2: About to process DSQL 2 ');
926: END IF;
927: DSQL_c := dbms_sql.open_cursor;
928: dbms_sql.parse(DSQL_c, DSQL_statement, dbms_sql.native);
929: dbms_sql.define_column(DSQL_c,1,DSQL_inventory_item_id);
943: cr.inventory_item_id := exists_id;
944: else
945: --Adding resolution of Item Id from Master in same batch (from Intf table)
946: IF l_inv_debug_level IN(101, 102) THEN
947: INVPUTLI.info('INVPAGI2: About to process DSQL 3 ');
948: END IF;
949: DSQL_Statement_Msii := 'SELECT msi.inventory_item_id
950: FROM mtl_system_items_interface msi, mtl_system_items_interface msii
951: WHERE msi.set_process_id = msii.set_process_id
985: -- No such row found. Close the cursor after
986: -- Assigning missing inventory_item_id from sequence
987:
988: IF l_inv_debug_level IN(101, 102) THEN
989: INVPUTLI.info('INVPAGI2: No match in MSI; Creating Inventory Item Id from sequence');
990: END IF;
991:
992: update MTL_SYSTEM_ITEMS_INTERFACE
993: set inventory_item_id = MTL_SYSTEM_ITEMS_S.nextval
1026: end if; /* cr.inventory_item_id is null */
1027:
1028: -- determine if item is in master org.
1029: IF l_inv_debug_level IN(101, 102) THEN
1030: INVPUTLI.info('INVPAGI2: Determining whether the item is in master org');
1031: END IF;
1032: if l_old_organization_id <> cr.organization_id then
1033: select mp.master_organization_id ,
1034: mp.starting_revision ,
1049: if (cr.revision is null) then
1050: cr.revision := default_rev;
1051: end if;
1052: IF l_inv_debug_level IN(101, 102) THEN
1053: INVPUTLI.info('cr.rev is ' || cr.revision);
1054: END IF;
1055: msiicount := 0;
1056:
1057: select count(*) into msiicount
1058: from mtl_system_items msii
1059: where cr.inventory_item_id = msii.inventory_item_id
1060: and msii.organization_id = master_org_id;
1061: IF l_inv_debug_level IN(101, 102) THEN
1062: INVPUTLI.info('Processing itemid '|| cr.inventory_item_id );
1063: INVPUTLI.info('with Org id '|| cr.organization_id );
1064: INVPUTLI.info('with segment1 '|| cr.segment1 );
1065: END IF;
1066: --assign master_org attribute defaults if in child org AND parent exists
1059: where cr.inventory_item_id = msii.inventory_item_id
1060: and msii.organization_id = master_org_id;
1061: IF l_inv_debug_level IN(101, 102) THEN
1062: INVPUTLI.info('Processing itemid '|| cr.inventory_item_id );
1063: INVPUTLI.info('with Org id '|| cr.organization_id );
1064: INVPUTLI.info('with segment1 '|| cr.segment1 );
1065: END IF;
1066: --assign master_org attribute defaults if in child org AND parent exists
1067:
1060: and msii.organization_id = master_org_id;
1061: IF l_inv_debug_level IN(101, 102) THEN
1062: INVPUTLI.info('Processing itemid '|| cr.inventory_item_id );
1063: INVPUTLI.info('with Org id '|| cr.organization_id );
1064: INVPUTLI.info('with segment1 '|| cr.segment1 );
1065: END IF;
1066: --assign master_org attribute defaults if in child org AND parent exists
1067:
1068: if ((master_org_id <> cr.organization_id) and (msiicount = 1)) then
1069: if rtn_status = 0 then
1070: IF INVPOPIF.g_source_org /*Added for bug 6372595*/
1071: THEN
1072: IF l_inv_debug_level IN(101, 102) THEN
1073: INVPUTLI.info('INVPAGI2: calling assign_master_defaults with set_id '||xset_id);
1074: END IF;
1075: rtn_status := INVPUTLI.assign_master_defaults(
1076: cr.transaction_id,
1077: cr.inventory_item_id,
1071: THEN
1072: IF l_inv_debug_level IN(101, 102) THEN
1073: INVPUTLI.info('INVPAGI2: calling assign_master_defaults with set_id '||xset_id);
1074: END IF;
1075: rtn_status := INVPUTLI.assign_master_defaults(
1076: cr.transaction_id,
1077: cr.inventory_item_id,
1078: cr.organization_id,
1079: master_org_id,
1092: else
1093: if ((master_org_id <> cr.organization_id) and (msiicount = 0)) then
1094: if rtn_status = 0 then
1095: IF l_inv_debug_level IN(101, 102) THEN
1096: INVPUTLI.info('INVPAGI2: Orphan found; about to call error ');
1097: INVPUTLI.info('INVPAGI2: Orphan found; checking in msii ');
1098: END IF;
1099:
1100: select count(*) into msiicount
1093: if ((master_org_id <> cr.organization_id) and (msiicount = 0)) then
1094: if rtn_status = 0 then
1095: IF l_inv_debug_level IN(101, 102) THEN
1096: INVPUTLI.info('INVPAGI2: Orphan found; about to call error ');
1097: INVPUTLI.info('INVPAGI2: Orphan found; checking in msii ');
1098: END IF;
1099:
1100: select count(*) into msiicount
1101: from mtl_system_items_interface
1123: if dumm_status < 0 then
1124: raise LOGGING_ERR;
1125: end if;
1126: IF l_inv_debug_level IN(101, 102) THEN
1127: INVPUTLI.info('INVPAGI2: Orphan found; error called '|| rtn_status);
1128: END IF;
1129: else
1130: rtn_status := INVPUTLI.predefault_child_master(
1131: cr.inventory_item_id,
1126: IF l_inv_debug_level IN(101, 102) THEN
1127: INVPUTLI.info('INVPAGI2: Orphan found; error called '|| rtn_status);
1128: END IF;
1129: else
1130: rtn_status := INVPUTLI.predefault_child_master(
1131: cr.inventory_item_id,
1132: cr.organization_id,
1133: master_org_id,
1134: err_text,
1141: end if; /*rtn_status = 0*/
1142: else
1143: if rtn_status = 0 then
1144: IF l_inv_debug_level IN(101, 102) THEN
1145: INVPUTLI.info('INVPAGI2: In the new-item-in-master case ');
1146: INVPUTLI.info('INVPAGI2: Calling assign_item_defaults with set_id '|| xset_id);
1147: END IF;
1148:
1149: rtn_status := INVPUTLI.assign_item_defaults(
1142: else
1143: if rtn_status = 0 then
1144: IF l_inv_debug_level IN(101, 102) THEN
1145: INVPUTLI.info('INVPAGI2: In the new-item-in-master case ');
1146: INVPUTLI.info('INVPAGI2: Calling assign_item_defaults with set_id '|| xset_id);
1147: END IF;
1148:
1149: rtn_status := INVPUTLI.assign_item_defaults(
1150: cr.inventory_item_id,
1145: INVPUTLI.info('INVPAGI2: In the new-item-in-master case ');
1146: INVPUTLI.info('INVPAGI2: Calling assign_item_defaults with set_id '|| xset_id);
1147: END IF;
1148:
1149: rtn_status := INVPUTLI.assign_item_defaults(
1150: cr.inventory_item_id,
1151: cr.organization_id,
1152: status_default,
1153: uom_default,