52: |
53: |
54: | Modified on 08-Aug-2005 by Kiran Konada
55: | bug# 4539578
56: | In R12, mtl_cross_references datamodel has been changed to
57: | mtl_cross_references_b and mtl_cross_references_tl
58: |
59: |
60: | Modified on 22-Sep-2005 Renga Kannan
53: |
54: | Modified on 08-Aug-2005 by Kiran Konada
55: | bug# 4539578
56: | In R12, mtl_cross_references datamodel has been changed to
57: | mtl_cross_references_b and mtl_cross_references_tl
58: |
59: |
60: | Modified on 22-Sep-2005 Renga Kannan
61: | Made Code changes for ATG Performance
988:
989: v_cto_cost_type_name cst_cost_types.cost_type%type;
990:
991: --kkonada R12
992: --for mtl_cross_references_b
993: --bug# 4539578
994:
995: TYPE cfg_item_id IS TABLE OF bom_cto_order_lines_upg.config_item_id%type;
996: TYPE org_id IS TABLE OF mtl_cross_references_b.organization_id%type;
992: --for mtl_cross_references_b
993: --bug# 4539578
994:
995: TYPE cfg_item_id IS TABLE OF bom_cto_order_lines_upg.config_item_id%type;
996: TYPE org_id IS TABLE OF mtl_cross_references_b.organization_id%type;
997: TYPE cross_reference_type IS TABLE OF mtl_cross_references_b.cross_reference_type%type;
998: TYPE cross_reference IS TABLE OF mtl_cross_references_b.cross_reference%type;
999: TYPE org_independent_flag IS TABLE OF mtl_cross_references_b.org_independent_flag%type;
1000:
993: --bug# 4539578
994:
995: TYPE cfg_item_id IS TABLE OF bom_cto_order_lines_upg.config_item_id%type;
996: TYPE org_id IS TABLE OF mtl_cross_references_b.organization_id%type;
997: TYPE cross_reference_type IS TABLE OF mtl_cross_references_b.cross_reference_type%type;
998: TYPE cross_reference IS TABLE OF mtl_cross_references_b.cross_reference%type;
999: TYPE org_independent_flag IS TABLE OF mtl_cross_references_b.org_independent_flag%type;
1000:
1001: t_cfg_item_id cfg_item_id;
994:
995: TYPE cfg_item_id IS TABLE OF bom_cto_order_lines_upg.config_item_id%type;
996: TYPE org_id IS TABLE OF mtl_cross_references_b.organization_id%type;
997: TYPE cross_reference_type IS TABLE OF mtl_cross_references_b.cross_reference_type%type;
998: TYPE cross_reference IS TABLE OF mtl_cross_references_b.cross_reference%type;
999: TYPE org_independent_flag IS TABLE OF mtl_cross_references_b.org_independent_flag%type;
1000:
1001: t_cfg_item_id cfg_item_id;
1002: t_organization_id org_id;
995: TYPE cfg_item_id IS TABLE OF bom_cto_order_lines_upg.config_item_id%type;
996: TYPE org_id IS TABLE OF mtl_cross_references_b.organization_id%type;
997: TYPE cross_reference_type IS TABLE OF mtl_cross_references_b.cross_reference_type%type;
998: TYPE cross_reference IS TABLE OF mtl_cross_references_b.cross_reference%type;
999: TYPE org_independent_flag IS TABLE OF mtl_cross_references_b.org_independent_flag%type;
1000:
1001: t_cfg_item_id cfg_item_id;
1002: t_organization_id org_id;
1003: t_cross_ref_type cross_reference_type;
2557: t_cross_ref_type,
2558: t_cross_ref,
2559: t_org_independent_flag
2560: from
2561: mtl_cross_references_b cr_b,
2562: bom_cto_src_orgs bcso,
2563: bom_cto_order_lines_upg bcolu
2564: where bcolu.config_item_id is not null
2565: and bcolu.status = 'BCSO'
2569: and bcso.model_item_id = bcolu.inventory_item_id
2570: and bcso.line_id = bcolu.line_id
2571: and NOT EXISTS
2572: (select NULL
2573: from mtl_cross_references
2574: where inventory_item_id = bcolu.config_item_id
2575: and (organization_id = bcso.organization_id
2576: or organization_id is null)); -- bugfix 1960994: added OR condition
2577:
2578:
2579: IF t_cross_ref_type.count <> 0 THEN
2580:
2581: FORALL i IN 1..t_cross_ref_type.count
2582: INSERT INTO MTL_CROSS_REFERENCES_B
2583: (
2584: INVENTORY_ITEM_ID
2585: ,ORGANIZATION_ID
2586: ,CROSS_REFERENCE_TYPE
2635: ,NULL --PROGRAM_ID
2636: ,SYSDATE --PROGRAM_UPDATE_DATE
2637: ,NULL --SOURCE_SYSTEM_ID
2638: ,1 --OBJECT_VERSION_NUMBER
2639: ,NULL --UOM_CODE due to ER#3215422. do not copy uom_code and revision_id attribute for mtl_cross_references
2640: ,NULL --REVISION_ID due to ER#3215422. do not copy uom_code and revision_id attribute for mtl_cross_references
2641: ,MTL_CROSS_REFERENCES_B_S.NEXTVAL --CROSS_REFERENCE_ID
2642: ,0 --EPC_GTIN_SERIAL
2643: ,NULL --ATTRIBUTE1
2636: ,SYSDATE --PROGRAM_UPDATE_DATE
2637: ,NULL --SOURCE_SYSTEM_ID
2638: ,1 --OBJECT_VERSION_NUMBER
2639: ,NULL --UOM_CODE due to ER#3215422. do not copy uom_code and revision_id attribute for mtl_cross_references
2640: ,NULL --REVISION_ID due to ER#3215422. do not copy uom_code and revision_id attribute for mtl_cross_references
2641: ,MTL_CROSS_REFERENCES_B_S.NEXTVAL --CROSS_REFERENCE_ID
2642: ,0 --EPC_GTIN_SERIAL
2643: ,NULL --ATTRIBUTE1
2644: ,NULL --ATTRIBUTE2
2637: ,NULL --SOURCE_SYSTEM_ID
2638: ,1 --OBJECT_VERSION_NUMBER
2639: ,NULL --UOM_CODE due to ER#3215422. do not copy uom_code and revision_id attribute for mtl_cross_references
2640: ,NULL --REVISION_ID due to ER#3215422. do not copy uom_code and revision_id attribute for mtl_cross_references
2641: ,MTL_CROSS_REFERENCES_B_S.NEXTVAL --CROSS_REFERENCE_ID
2642: ,0 --EPC_GTIN_SERIAL
2643: ,NULL --ATTRIBUTE1
2644: ,NULL --ATTRIBUTE2
2645: ,NULL --ATTRIBUTE3
2657: ,NULL --ATTRIBUTE15
2658: ,NULL --ATTRIBUTE_CATEGORY
2659: );
2660:
2661: WriteToLog('Inserted rows into mtl_cross_references_b:: '||sql%rowcount,2);
2662:
2663: FORALL i IN 1..t_cfg_item_id.count
2664: INSERT INTO mtl_cross_references_tl (
2665: last_update_login
2660:
2661: WriteToLog('Inserted rows into mtl_cross_references_b:: '||sql%rowcount,2);
2662:
2663: FORALL i IN 1..t_cfg_item_id.count
2664: INSERT INTO mtl_cross_references_tl (
2665: last_update_login
2666: ,description
2667: ,creation_date
2668: ,created_by
2681: mtl_cross.cross_reference_id,
2682: l.language_code,
2683: userenv('lang')
2684: FROM fnd_languages l,
2685: mtl_cross_references_b mtl_cross,
2686: mtl_system_items_tl mtl
2687: WHERE mtl_cross.inventory_item_id = t_cfg_item_id(i)
2688: AND mtl_cross.inventory_item_id = mtl.inventory_item_id
2689: AND mtl_cross.organization_id = mtl.organization_id
2689: AND mtl_cross.organization_id = mtl.organization_id
2690: AND l.language_code = mtl.language
2691: AND l.installed_flag in ('I', 'B')
2692: AND NOT EXISTS (SELECT null
2693: FROM mtl_cross_references_tl t
2694: WHERE t.cross_reference_id = mtl_cross.cross_reference_id
2695: AND t.language = l.language_code);
2696:
2697: WriteToLog('Inserted rows into mtl_cross_references_tl:: '||sql%rowcount,2);
2693: FROM mtl_cross_references_tl t
2694: WHERE t.cross_reference_id = mtl_cross.cross_reference_id
2695: AND t.language = l.language_code);
2696:
2697: WriteToLog('Inserted rows into mtl_cross_references_tl:: '||sql%rowcount,2);
2698:
2699:
2700: END IF; --t_cross_ref_type.count()
2701: