1 PACKAGE MSC_CL_SCE_COLLECTION AUTHID CURRENT_USER AS
2 /* $Header: MSCXCSCS.pls 120.3 2011/08/18 12:23:46 vjuluri ship $ */
3
4 -- ===== Constants ====
5 G_SUPPLIER CONSTANT NUMBER := 1;
6 G_CUSTOMER CONSTANT NUMBER := 2;
7 G_ORGANIZATION CONSTANT NUMBER := 3;
8 G_MY_COMPANY_ID CONSTANT NUMBER := 1;
9 G_CONF_APS CONSTANT NUMBER := 1;
10 G_CONF_APS_SCE CONSTANT NUMBER := 2;
11 G_CONF_SCE CONSTANT NUMBER := 3;
12 G_ERROR CONSTANT NUMBER := 2;
13 G_OEM_ID CONSTANT NUMBER := 1;
14
15 NO_USER_COMPANY CONSTANT NUMBER := 1;
16 COMPANY_ONLY CONSTANT NUMBER := 2;
17 USER_AND_COMPANY CONSTANT NUMBER := 3;
18
19
20 -- ===== Data types for Bulk Collections ====
21 TYPE companyNames IS TABLE of msc_companies.company_name%TYPE;
22 TYPE companySites IS TABLE of msc_company_sites.company_site_name%TYPE;
23 -- TYPE customerClassCodes IS TABLE of msc_companies.customer_class_code%TYPE;
24 -- TYPE calendarCodes IS TABLE of msc_companies.calendar_code%TYPE;
25 TYPE number_arr IS TABLE OF NUMBER;
26 TYPE date_arr IS TABLE OF DATE;
27 TYPE char3_arr IS TABLE OF VARCHAR2(3);
28 TYPE char_arr IS TABLE OF VARCHAR2(1);
29 TYPE locationCodes IS TABLE OF msc_company_sites.location%TYPE;
30
31 TYPE partnerAddresses IS TABLE OF msc_trading_partner_sites.partner_address%TYPE;
32 TYPE countries IS TABLE OF msc_trading_partner_sites.country%TYPE;
33 TYPE states IS TABLE OF msc_trading_partner_sites.state%TYPE;
34 TYPE cities IS TABLE OF msc_trading_partner_sites.city%TYPE;
35 TYPE postalCodes IS TABLE OF msc_trading_partner_sites.postal_code%TYPE;
36 TYPE items IS TABLE OF msc_system_items.item_name%TYPE;
37 TYPE rowids IS TABLE OF VARCHAR2(100);
38 TYPE descriptions IS TABLE OF msc_item_customers.description%TYPE;
39 TYPE uomCodes IS TABLE OF msc_item_customers.uom_code%TYPE;
40 TYPE plannerCodes IS TABLE OF msc_item_customers.planner_code%TYPE;
41 TYPE deliveryCalCodes IS TABLE OF msc_item_suppliers.delivery_calendar_code%TYPE;
42 TYPE addressLines IS TABLE OF msc_company_sites.address1%TYPE;
43 TYPE counties IS TABLE OF msc_st_trading_partner_sites.county%TYPE;
44 TYPE provinces IS TABLE OF msc_st_trading_partner_sites.province%TYPE;
45 TYPE users IS TABLE OF fnd_user.user_name%TYPE;
46
47 FUNCTION SCE_TRANSFORM_KEYS(p_instance_id NUMBER,
48 p_current_user NUMBER,
49 p_current_date DATE,
50 p_last_collection_id NUMBER,
51 p_is_incremental_refresh BOOLEAN,
52 p_is_complete_refresh BOOLEAN,
53 p_is_partial_refresh BOOLEAN,
54 p_is_cont_refresh BOOLEAN,
55 p_supplier_enabled NUMBER,
56 p_customer_enabled NUMBER) RETURN BOOLEAN;
57 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2);
58
59 PROCEDURE CREATE_NEW_COMPANIES( p_current_user NUMBER,
60 p_current_date DATE,
61 p_last_collection_id NUMBER );
62
63 PROCEDURE UPDATE_COMPANY_NAMES( p_current_user NUMBER, -- Bug#12863892 fp of bug 9645763
64 p_current_date DATE);
65
66 PROCEDURE UPDATE_COMPANY_SITE_NAMES;
67 PROCEDURE CREATE_NEW_RELATIONSHIPS;
68 PROCEDURE POPULATE_COMPANY_ID_LID;
69 PROCEDURE CREATE_NEW_COMPANY_SITES;
70 FUNCTION CLEANSE_DATA_FOR_SCE(p_instance_id NUMBER,
71 p_my_company VARCHAR2) RETURN BOOLEAN;
72 PROCEDURE COLLECT_COMPANY_SITES;
73 PROCEDURE POPULATE_COMPANY_SITE_ID_LID;
74 PROCEDURE POPULATE_TP_MAP_TABLE(p_instance_id NUMBER);
75 -- PROCEDURE CREATE_NEW_COMPANY_LOCATIONS (p_instance_id NUMBER);
76 -- PROCEDURE COLLECT_COMPANY_LOCATIONS (p_instance_id NUMBER);
77 PROCEDURE CLEANSE_TP_ITEMS (p_instance_id NUMBER);
78 PROCEDURE LOAD_ITEM_CUSTOMERS (p_instance_id NUMBER);
79 -- PROCEDURE LOAD_SCE_SUPPLIER_CAPACITY (p_instance_id NUMBER,
80 -- p_current_user NUMBER,
81 -- p_current_date DATE,
82 -- p_last_collection_id NUMBER);
83
84 FUNCTION GET_MY_COMPANY return VARCHAR2;
85
86 PROCEDURE PULL_USER_COMPANY(p_dblink varchar2,
87 p_instance_id NUMBER,
88 p_return_status OUT NOCOPY BOOLEAN,
89 p_user_company_mode NUMBER);
90
91 PROCEDURE LOAD_USER_COMPANY (p_sr_instance_id NUMBER);
92
93 PROCEDURE PROCESS_COMPANY_CHANGE(p_status OUT NOCOPY NUMBER);
94
95 END MSC_CL_SCE_COLLECTION;
[Home] [Help]