PACKAGE BODY: APPS.PA_MULTI_ORG

Source

1 PACKAGE BODY pa_multi_org AS
2 /* $Header: PAMORGB.pls 115.1 99/07/16 15:08:11 porting ship  $ */
3 
4   PROCEDURE copy_seed_data ( x_rec_count  OUT NUMBER
5                            , x_err_text   OUT VARCHAR2 )
6   IS
7     x_org_id     NUMBER(15);
8     x_user       NUMBER(15) := 1;
9     x_login      NUMBER(15) := 0;
10     l_rec_count  NUMBER := 0;
11     x_stage      VARCHAR2(100);
12 
13   BEGIN
14     -- Get operating unit ORG_ID
15     x_stage := 'SELECT ORG_ID FROM PA_IMPLEMENTATIONS';
16     SELECT  org_id
17       INTO  x_org_id
18       FROM  pa_implementations;
19 
20     -- Copy Function Transactions
21     x_stage := 'INSERT INTO PA_FUNCTION_TRANSACTIONS';
22     INSERT INTO pa_function_transactions(
23        application_id
24     ,  function_code
25     ,  function_transaction_code
26     ,  function_transaction_name
27     ,  last_update_date
28     ,  last_updated_by
29     ,  creation_date
30     ,  created_by
31     ,  last_update_login
32     ,  enabled_flag
33     ,  description
34     ,  org_id )
35     SELECT
36      	    ft.application_id
37     ,       ft.function_code
38     ,       ft.function_transaction_code
39     ,       ft.function_transaction_name
40     ,       sysdate
41     ,       x_user
42     ,       sysdate
43     ,       x_user
44     ,       x_login
45     ,       ft.enabled_flag
46     ,       ft.description
47     ,       x_org_id
48       FROM  pa_function_transactions_all ft
49      WHERE  org_id = -3113
50     AND NOT EXISTS (
51        SELECT NULL
52          FROM pa_function_transactions
53         WHERE org_id = x_org_id
54           AND application_id = ft.application_id
55           AND function_code = ft.function_code
56           AND function_transaction_code = ft.function_transaction_code );
57 
58     l_rec_count := l_rec_count + SQL%ROWCOUNT;
59 
60     -- Copy Billing Assignments
61     x_stage := 'INSERT INTO PA_BILLING_ASSIGNMENTS';
62     INSERT INTO pa_billing_assignments(
63        billing_assignment_id
64     ,  billing_extension_id
65     ,  project_type
66     ,  project_id
67     ,  top_task_id
68     ,  amount
69     ,  percentage
70     ,  active_flag
71     ,  last_update_date
72     ,  last_updated_by
73     ,  creation_date
74     ,  created_by
75     ,  last_update_login
76     ,  distribution_rule
77     ,  org_id )
78     SELECT
79             pa_billing_assignments_s.NEXTVAL
80     ,       ba.billing_extension_id
81     ,       ba.project_type
82     ,       ba.project_id
83     ,       ba.top_task_id
84     ,       ba.amount
85     ,       ba.percentage
86     ,       ba.active_flag
87     ,       sysdate
88     ,       x_user
89     ,       sysdate
90     ,       x_user
91     ,       x_login
92     ,       ba.distribution_rule
93     ,       x_org_id
94       FROM  pa_billing_assignments_all ba
95      WHERE  org_id = -3113
96     AND NOT EXISTS (
97        SELECT NULL
98          FROM pa_billing_assignments
99         WHERE org_id = x_org_id
100           AND distribution_rule = ba.distribution_rule
101           AND billing_extension_id = ba.billing_extension_id
102           AND project_type = ba.project_type
103           AND project_id = ba.project_id
104           AND top_task_id = ba.top_task_id );
105 
106     l_rec_count := l_rec_count + SQL%ROWCOUNT;
107 
108     IF ( l_rec_count > 0 ) THEN
109       COMMIT;
110     END IF;
111 
112     x_rec_count := l_rec_count;
113 
114   EXCEPTION
115     WHEN  NO_DATA_FOUND  THEN
116       x_rec_count := -1403;
117       x_err_text :=  x_stage || ' - ' || SQLERRM(-1403);
118     WHEN  OTHERS  THEN
119       x_rec_count := SQLCODE;
120       x_err_text :=  x_stage || ' - ' || SQLERRM(SQLCODE);
121 
122   END copy_seed_data;
123 
124 END pa_multi_org;