[Home] [Help]
192: BEGIN
193:
194: SELECT to_char(sysdate,'DD-MON-YYYY HH24:MI:SS : ')
195: INTO l_date_time_stamp
196: FROM sys.dual;
197:
198: RETURN l_date_time_stamp;
199: END date_time_stamp;
200:
286: ) IS
287: BEGIN
288: FOR i in p_inst_tbl.FIRST .. p_inst_tbl.LAST LOOP
289: select CSI_ITEM_INSTANCES_H_S.nextval
290: into p_inst_hist_tbl(i) from dual;
291: --
292: p_inst_rec_tab.INSTANCE_ID(i) := p_inst_tbl(i).INSTANCE_ID;
293: p_inst_rec_tab.INSTANCE_NUMBER(i) := p_inst_tbl(i).INSTANCE_NUMBER;
294: p_inst_rec_tab.EXTERNAL_REFERENCE(i) := p_inst_tbl(i).EXTERNAL_REFERENCE;
374: ) IS
375: BEGIN
376: FOR i in p_ii_relationship_tbl.FIRST .. p_ii_relationship_tbl.LAST LOOP
377: select CSI_II_RELATIONSHIPS_H_S.nextval
378: into p_rel_hist_tbl(i) from dual;
379: --
380: p_ii_relationship_rec_tab.RELATIONSHIP_ID(i) := p_ii_relationship_tbl(i).RELATIONSHIP_ID;
381: p_ii_relationship_rec_tab.RELATIONSHIP_TYPE_CODE(i) := p_ii_relationship_tbl(i).RELATIONSHIP_TYPE_CODE;
382: p_ii_relationship_rec_tab.OBJECT_ID(i) := p_ii_relationship_tbl(i).OBJECT_ID;
415: ) IS
416: BEGIN
417: FOR i in p_version_label_tbl.FIRST .. p_version_label_tbl.LAST LOOP
418: select CSI_I_VERSION_LABELS_H_S.nextval
419: into p_ver_label_hist_tbl(i) from dual;
420: --
421: p_version_label_rec_tab.version_label_id(i) := p_version_label_tbl(i).version_label_id;
422: p_version_label_rec_tab.instance_id(i) := p_version_label_tbl(i).instance_id;
423: p_version_label_rec_tab.version_label(i) := p_version_label_tbl(i).version_label;
452: ) IS
453: BEGIN
454: FOR i in p_party_tbl.FIRST .. p_party_tbl.LAST LOOP
455: select CSI_I_PARTIES_H_S.nextval
456: into p_party_hist_tbl(i) from dual;
457: --
458: p_party_rec_tab.instance_party_id(i) := p_party_tbl(i).instance_party_id;
459: p_party_rec_tab.instance_id(i) := p_party_tbl(i).instance_id;
460: p_party_rec_tab.party_source_table(i) := p_party_tbl(i).party_source_table;
494: ) IS
495: BEGIN
496: FOR i in p_account_tbl.FIRST .. p_account_tbl.LAST LOOP
497: select CSI_IP_ACCOUNTS_H_S.nextval
498: into p_account_hist_tbl(i) from dual;
499: --
500: p_account_rec_tab.ip_account_id(i) := p_account_tbl(i).ip_account_id;
501: p_account_rec_tab.parent_tbl_index(i) := p_account_tbl(i).parent_tbl_index;
502: p_account_rec_tab.instance_party_id(i) := p_account_tbl(i).instance_party_id;
537: ) IS
538: BEGIN
539: FOR i in p_org_tbl.FIRST .. p_org_tbl.LAST LOOP
540: select CSI_I_ORG_ASSIGNMENTS_H_S.nextval
541: into p_org_hist_tbl(i) from dual;
542: --
543: p_org_units_rec_tab.instance_ou_id(i) := p_org_tbl(i).instance_ou_id;
544: p_org_units_rec_tab.instance_id(i) := p_org_tbl(i).instance_id;
545: p_org_units_rec_tab.operating_unit_id(i) := p_org_tbl(i).operating_unit_id;
574: ) IS
575: BEGIN
576: FOR i in p_pricing_tbl.FIRST .. p_pricing_tbl.LAST LOOP
577: select CSI_I_PRICING_ATTRIBS_H_S.nextval
578: into p_pricing_hist_tbl(i) from dual;
579: --
580: p_pricing_rec_tab.pricing_attribute_id(i) := p_pricing_tbl(i).pricing_attribute_id;
581: p_pricing_rec_tab.instance_id(i) := p_pricing_tbl(i).instance_id;
582: p_pricing_rec_tab.active_start_date(i) := p_pricing_tbl(i).active_start_date;
711:
712: BEGIN
713: FOR i in p_ext_attr_tbl.FIRST .. p_ext_attr_tbl.LAST LOOP
714: select CSI_IEA_VALUES_H_S.nextval
715: into p_ext_hist_tbl(i) from dual;
716: --
717: p_ext_attr_rec_tab.attribute_value_id(i) := p_ext_attr_tbl(i).attribute_value_id;
718: p_ext_attr_rec_tab.instance_id(i) := p_ext_attr_tbl(i).instance_id;
719: p_ext_attr_rec_tab.attribute_id(i) := p_ext_attr_tbl(i).attribute_id;
750:
751: BEGIN
752: FOR i in p_asset_tbl.FIRST .. p_asset_tbl.LAST LOOP
753: select CSI_I_ASSETS_H_S.nextval
754: into p_asset_hist_tbl(i) from dual;
755: --
756: p_asset_rec_tab.instance_asset_id(i) := p_asset_tbl(i).instance_asset_id;
757: p_asset_rec_tab.instance_id(i) := p_asset_tbl(i).instance_id;
758: p_asset_rec_tab.fa_asset_id(i) := p_asset_tbl(i).fa_asset_id;
1084: l_csi_txn_rec.source_header_ref := 'Forward Synch';
1085:
1086: SELECT csi_ii_forward_sync_temp_s.nextval
1087: INTO l_csi_txn_rec.source_line_ref_id
1088: FROM sys.dual;
1089:
1090: FOR fs_rec IN fs_cur
1091: LOOP
1092:
1444: v_txn_type_id := correction_txn_type_id;
1445:
1446: SELECT csi_transactions_s.nextval
1447: INTO l_txn_id
1448: FROM sys.dual;
1449:
1450: --
1451: BEGIN
1452: p_instance_rec := p_temp_instance_rec;
3313: BEGIN
3314:
3315: SELECT fnd_Profile.value('csi_upgrading_from_release')
3316: INTO l_release
3317: FROM sys.dual;
3318:
3319: decode_queue;
3320:
3321: FOR q_rec IN q_cur
3428: l_missing_tbl.delete;
3429:
3430: SELECT fnd_Profile.value('CSI_UPGRADING_FROM_RELEASE')
3431: INTO l_release
3432: FROM sys.dual;
3433: --
3434: IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
3435: csi_gen_utility_pvt.populate_install_param_rec;
3436: END IF;
3442:
3443: -- check if processed
3444: BEGIN
3445: SELECT 'Y' INTO l_processed_flag
3446: FROM sys.dual
3447: WHERE exists(
3448: SELECT '1' FROM csi_transactions
3449: WHERE inv_material_transaction_id = wip_txn_rec.mtl_txn_id);
3450: l_processed := true;
3457: -- check if errors
3458: l_in_error := false;
3459: BEGIN
3460: SELECT 'Y' INTO l_txn_error_flag
3461: FROM sys.dual
3462: WHERE EXISTS (
3463: SELECT '1' FROM csi_txn_errors
3464: WHERE inv_material_transaction_id = wip_txn_rec.mtl_txn_id
3465: AND processed_flag in ('E', 'R'));
3472: -- check if pending in queue
3473: IF NOT(l_in_error) THEN
3474: BEGIN
3475: SELECT 'Y' INTO l_pending_msg_found
3476: FROM sys.dual
3477: WHERE exists (
3478: SELECT '1'
3479: FROM csi_xnp_msgs_temp
3480: WHERE source_type = 'MTL_TRANSACTION_ID'
3563: l_missing_tbl.delete;
3564:
3565: SELECT fnd_Profile.value('csi_upgrading_from_release')
3566: INTO l_release
3567: FROM sys.dual;
3568:
3569: FOR all_txn_rec in all_txn_cur(
3570: p_serial_number => p_serial_number,
3571: p_item_id => p_item_id)
3578: -- check if processed
3579: BEGIN
3580: IF all_txn_rec.mtl_action_id in (2,3,28) THEN
3581: SELECT 'Y' INTO l_processed_flag
3582: FROM sys.dual
3583: WHERE exists(
3584: SELECT '1' FROM csi_transactions
3585: WHERE (inv_material_transaction_id = all_txn_rec.mtl_txn_id
3586: OR
3586: OR
3587: inv_material_transaction_id = all_txn_rec.mtl_xfer_txn_id));
3588: ELSE
3589: SELECT 'Y' INTO l_processed_flag
3590: FROM sys.dual
3591: WHERE exists(
3592: SELECT '1' FROM csi_transactions
3593: WHERE inv_material_transaction_id = all_txn_rec.mtl_txn_id);
3594: END IF;
3606: l_in_error := false;
3607: BEGIN
3608: IF all_txn_rec.mtl_action_id in (2,3,28) THEN
3609: SELECT 'Y' INTO l_txn_error_flag
3610: FROM sys.dual
3611: WHERE EXISTS (
3612: SELECT '1' FROM csi_txn_errors
3613: WHERE (inv_material_transaction_id = all_txn_rec.mtl_txn_id
3614: OR
3615: inv_material_transaction_id = all_txn_rec.mtl_xfer_txn_id)
3616: AND processed_flag in ('E', 'R'));
3617: ELSE
3618: SELECT 'Y' INTO l_txn_error_flag
3619: FROM sys.dual
3620: WHERE EXISTS (
3621: SELECT '1' FROM csi_txn_errors
3622: WHERE inv_material_transaction_id = all_txn_rec.mtl_txn_id
3623: AND processed_flag in ('E', 'R'));
3631: -- check if pending in queue
3632: IF NOT(l_in_error) THEN
3633: BEGIN
3634: SELECT 'Y' INTO l_pending_msg_found
3635: FROM sys.dual
3636: WHERE exists (
3637: SELECT '1'
3638: FROM csi_xnp_msgs_temp
3639: WHERE source_type = 'MTL_TRANSACTION_ID'
4055: log(date_time_stamp||' begin get_srldata');
4056:
4057: SELECT fnd_Profile.value('csi_upgrading_from_release')
4058: INTO l_release
4059: FROM sys.dual;
4060: --
4061: IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
4062: csi_gen_utility_pvt.populate_install_param_rec;
4063: END IF;
5496:
5497: IF l_progressed THEN
5498: SELECT csi_ii_forward_sync_temp_s.nextval
5499: INTO l_seq_value
5500: FROM sys.dual;
5501: END IF;
5502:
5503: commit;
5504:
8912: decode_queue;
8913:
8914: SELECT fnd_Profile.value('csi_upgrading_from_release')
8915: INTO l_release
8916: FROM sys.dual;
8917: --
8918: IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
8919: csi_gen_utility_pvt.populate_install_param_rec;
8920: END IF;
9059: l_txn_rec.transaction_date := all_txn_rec.mtl_txn_date;
9060:
9061: BEGIN
9062: SELECT 'X' INTO l_txn_is_in_csi
9063: FROM sys.dual
9064: WHERE exists (
9065: SELECT 'Y' FROM csi_transactions
9066: WHERE inv_material_transaction_id = all_txn_rec.mtl_txn_id);
9067: l_txn_rec.inv_material_transaction_id := null;
9075: END IF;
9076:
9077: BEGIN
9078: SELECT 'Y' INTO l_pending_msg_found
9079: FROM sys.dual
9080: WHERE exists (
9081: SELECT '1'
9082: FROM csi_xnp_msgs_temp
9083: WHERE source_type = 'MTL_TRANSACTION_ID'
9096: BEGIN
9097: IF all_txn_rec.mtl_action_id in (2,3,28) THEN
9098:
9099: SELECT 'Y' INTO l_pending_err_found
9100: FROM sys.dual
9101: WHERE exists (
9102: SELECT '1'
9103: FROM csi_txn_errors
9104: WHERE (inv_material_transaction_id = all_txn_rec.mtl_txn_id
9108:
9109: ELSE
9110:
9111: SELECT 'Y' INTO l_pending_err_found
9112: FROM sys.dual
9113: WHERE exists (
9114: SELECT '1'
9115: FROM csi_txn_errors
9116: WHERE inv_material_transaction_id = all_txn_rec.mtl_txn_id
10732: RAISE Comp_error;
10733: END IF;
10734: --
10735: select CSI_TRANSACTIONS_S.nextval
10736: into l_txn_id from dual;
10737: --
10738: INSERT INTO CSI_TRANSACTIONS(
10739: TRANSACTION_ID
10740: ,TRANSACTION_DATE
11367: commit;
11368: END IF;
11369: --
11370: select CSI_TRANSACTIONS_S.nextval
11371: into v_txn_id from dual;
11372: --
11373: OPEN CSI_CUR;
11374: LOOP
11375: FETCH CSI_CUR BULK COLLECT INTO
11401: l_ctr := l_ctr + 1;
11402: l_inst_tbl(l_ctr) := instance_id_mig(i);
11403: --
11404: select CSI_ITEM_INSTANCES_H_S.nextval
11405: into l_inst_hist_tbl(l_ctr) from dual;
11406: --
11407: -- For this instance_id get the children
11408: l_child_tbl.DELETE;
11409: --
11417: l_ctr := l_ctr + 1;
11418: l_inst_tbl(l_ctr) := l_child_tbl(rel_count).subject_id;
11419: --
11420: select CSI_ITEM_INSTANCES_H_S.nextval
11421: into l_inst_hist_tbl(l_ctr) from dual;
11422: END LOOP;
11423: END IF;
11424: Exception
11425: when Process_next then
11761: Raise comp_error;
11762: End;
11763: --
11764: select CSI_TRANSACTIONS_S.nextval
11765: into v_srl_txn_id from dual;
11766: --
11767: select CSI_TRANSACTIONS_S.nextval
11768: into v_lot_txn_id from dual;
11769: --
11764: select CSI_TRANSACTIONS_S.nextval
11765: into v_srl_txn_id from dual;
11766: --
11767: select CSI_TRANSACTIONS_S.nextval
11768: into v_lot_txn_id from dual;
11769: --
11770: /* commenting the following cursors for the bug 5989350 ,Since there is
11771: no need to nullify the serial and lot number for the instances
11772: which are already shipped to customers */
11801: l_inst_tbl(l_ctr) := instance_id_mig(i);
11802: l_srl_tbl(l_ctr) := serial_number_mig(i);
11803: --
11804: select CSI_ITEM_INSTANCES_H_S.nextval
11805: into l_inst_hist_tbl(l_ctr) from dual;
11806: END IF;
11807: --
11808: END LOOP;
11809: IF l_inst_tbl.count > 0 THEN
11885: l_inst_tbl(l_ctr) := instance_id_mig(i);
11886: l_lot_tbl(l_ctr) := lot_number_mig(i);
11887: --
11888: select CSI_ITEM_INSTANCES_H_S.nextval
11889: into l_inst_hist_tbl(l_ctr) from dual;
11890: END IF;
11891: END LOOP;
11892: --
11893: IF l_inst_tbl.count > 0 THEN
11966: l_ctr := l_ctr + 1;
11967: l_inst_tbl(l_ctr) := instance_id_mig(i);
11968: --
11969: select CSI_ITEM_INSTANCES_H_S.nextval
11970: into l_inst_hist_tbl(l_ctr) from dual;
11971: END IF;
11972: END LOOP;
11973: IF l_inst_tbl.count > 0 THEN
11974: -- Bulk Update Instances
12044: l_ctr := l_ctr + 1;
12045: l_inst_tbl(l_ctr) := instance_id_mig(i);
12046: --
12047: select CSI_ITEM_INSTANCES_H_S.nextval
12048: into l_inst_hist_tbl(l_ctr) from dual;
12049: END IF;
12050: END LOOP;
12051: --
12052: IF l_inst_tbl.count > 0 THEN
12376: --
12377: Begin
12378: select CSI_TRANSACTIONS_S.nextval
12379: into v_txn_id
12380: from DUAL;
12381: End;
12382: --
12383: Begin
12384: INSERT INTO CSI_TRANSACTIONS(
12485: --
12486: Begin
12487: select csi_item_instances_s.nextval
12488: into v_instance_id
12489: from DUAL;
12490: End;
12491: --
12492: -- Insert into CSI_ITEM_INSTANCES
12493: Begin
12579: -- Use the same instance record to create the history
12580: Begin
12581: select CSI_ITEM_INSTANCES_H_S.nextval
12582: into v_ins_history_id
12583: from DUAL;
12584: End;
12585: --
12586: Begin
12587: INSERT INTO CSI_ITEM_INSTANCES_H(
12716: --
12717: Begin
12718: select CSI_I_PARTIES_S.nextval
12719: into v_ins_party_id
12720: from DUAL;
12721: End;
12722: Begin
12723: INSERT INTO CSI_I_PARTIES(
12724: INSTANCE_PARTY_ID
12766: -- Insert into CSI_I_PARTIES_H
12767: Begin
12768: select CSI_I_PARTIES_H_S.nextval
12769: into v_ins_party_history_id
12770: from DUAL;
12771: End;
12772: -- Insert into CSI_I_PARTIES_H
12773: Begin
12774: INSERT INTO CSI_I_PARTIES_H(
12858: --
12859: Begin
12860: select CSI_TRANSACTIONS_S.nextval
12861: into v_txn_id
12862: from DUAL;
12863: End;
12864: --
12865: Begin
12866: INSERT INTO CSI_TRANSACTIONS(
12967: --
12968: Begin
12969: select csi_item_instances_s.nextval
12970: into v_instance_id
12971: from DUAL;
12972: End;
12973: --
12974: -- Insert into CSI_ITEM_INSTANCES
12975: Begin
13065: -- Use the same instance record to create the history
13066: Begin
13067: select CSI_ITEM_INSTANCES_H_S.nextval
13068: into v_ins_history_id
13069: from DUAL;
13070: End;
13071: --
13072: Begin
13073: INSERT INTO CSI_ITEM_INSTANCES_H(
13210: --
13211: Begin
13212: select CSI_I_PARTIES_S.nextval
13213: into v_ins_party_id
13214: from DUAL;
13215: End;
13216: Begin
13217: INSERT INTO CSI_I_PARTIES(
13218: INSTANCE_PARTY_ID
13260: -- Insert into CSI_I_PARTIES_H
13261: Begin
13262: select CSI_I_PARTIES_H_S.nextval
13263: into v_ins_party_history_id
13264: from DUAL;
13265: End;
13266: -- Insert into CSI_I_PARTIES_H
13267: Begin
13268: INSERT INTO CSI_I_PARTIES_H(
13440: IF l_last_mtl_txn_id is not null AND l_last_mtl_type_id = 15 THEN
13441: BEGIN
13442: SELECT 'Y'
13443: INTO l_last_rma_processed
13444: FROM sys.dual
13445: WHERE exists
13446: (SELECT '1' FROM csi_transactions
13447: WHERE inv_material_transaction_id = l_last_mtl_txn_id);
13448: EXCEPTION
13845: --
13846: Begin
13847: select CSI_TRANSACTIONS_S.nextval
13848: into v_txn_id
13849: from DUAL;
13850: End;
13851: --
13852: Begin
13853: INSERT INTO CSI_TRANSACTIONS(
13920: -- to populate the History Record.
13921: Begin
13922: select CSI_TRANSACTIONS_S.nextval
13923: into v_txn_id
13924: from DUAL;
13925: End;
13926: --
13927: Begin
13928: INSERT INTO CSI_TRANSACTIONS(
13960: --
13961: Begin
13962: select csi_item_instances_s.nextval
13963: into v_instance_id
13964: from DUAL;
13965: End;
13966: --
13967: -- Insert into CSI_ITEM_INSTANCES
13968: Begin
14090: -- Use the same instance record to create the history
14091: Begin
14092: select CSI_ITEM_INSTANCES_H_S.nextval
14093: into v_ins_history_id
14094: from DUAL;
14095: End;
14096: --
14097: Begin
14098: INSERT INTO CSI_ITEM_INSTANCES_H(
14259: --
14260: Begin
14261: select CSI_I_PARTIES_S.nextval
14262: into v_ins_party_id
14263: from DUAL;
14264: End;
14265: Begin
14266: INSERT INTO CSI_I_PARTIES(
14267: INSTANCE_PARTY_ID
14309: -- Insert into CSI_I_PARTIES_H
14310: Begin
14311: select CSI_I_PARTIES_H_S.nextval
14312: into v_ins_party_history_id
14313: from DUAL;
14314: End;
14315: -- Insert into CSI_I_PARTIES_H
14316: Begin
14317: INSERT INTO CSI_I_PARTIES_H(
14601: when others then
14602: Raise comp_error;
14603: End;
14604: select CSI_TRANSACTIONS_S.nextval
14605: into l_txn_id from dual;
14606: --
14607: OPEN CSI_CUR;
14608: LOOP
14609: FETCH CSI_CUR BULK COLLECT INTO
15676: BEGIN
15677:
15678: SELECT fnd_Profile.value('csi_upgrading_from_release')
15679: INTO l_release
15680: FROM sys.dual;
15681:
15682: FOR err_rec IN err_cur
15683: LOOP
15684:
15683: LOOP
15684:
15685: BEGIN
15686: SELECT 'Y' INTO l_txn_processed
15687: FROM sys.dual
15688: WHERE exists (
15689: SELECT 'X' FROM csi_transactions
15690: WHERE inv_material_transaction_id = err_rec.inv_material_transaction_id);
15691: