1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
| EXECUTE immediate 'INSERT into EXTRAC_REGL_' || BASE || '_GEN_NON_SOLD (
select
AR_CASH_RECEIPTS_ALL.ORG_ID ID_SOC_JUR,
HR_ALL_ORGANIZATION_UNITS.NAME SOC_JUR,
AR_CASH_RECEIPTS_ALL.RECEIPT_NUMBER NUM_RGL,
AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID ID_RGL,
AR_CASH_RECEIPTS_ALL.DOC_SEQUENCE_VALUE NUM_DOC_RGL,
to_char(AR_CASH_RECEIPTS_ALL.RECEIPT_DATE, ''DD-MM-YYYY'') DATE_RGL,
to_char(AR_PAYMENT_SCHEDULES_ALL.DUE_DATE, ''DD-MM-YYYY'') DATE_ECH_RGL,
DECODE(''' || PARAM_DATE_GL ||
''',NULL,to_char(AR_CASH_RECEIPT_HISTORY_ALL.GL_DATE,''DD-MM-YYYY''),''' ||
to_char(to_date(to_char(PARAM_DATE_GL, 'DD-MON-YYYY'), 'DD-MON-YYYY'), 'DD-MM-YYYY') || ''') DATE_COMPTABLE,
' || PARAM_EXERCICE_INTEG || ' EXERCICE_INTEGR,
AR_CASH_RECEIPTS_ALL.CURRENCY_CODE DEVIsE_RGL,
HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER CLIENT_COMPTABLE,
HZ_CUST_SITE_USES_ALL.LOCATION CLIENT_FACTURE,
AR_RECEIPT_METHODS.NAME MODE_PMT,
AR_CASH_RECEIPTS_ALL.COMMENTS COMM_RGL,
AP_BANK_BRANCHES.BANK_NUMBER CODE_BANQUE,
AP_BANK_BRANCHES.BANK_NUM CODE_AG_BANQUE,
AP_BANK_ACCOUNTS_ALL.BANK_ACCOUNT_NUM NUM_CPTE_BANQUE,
substr(HZ_CUST_SITE_USES_ALL.LOCATION, 1, 3) CODE_AGENCE,
decode(CC__SOCIETE.CD_CPTA_BASE,
1,
''HOLDING'',
2,
''EIDF'',
3,
''ECEM'',
4,
''ENE'',
5,
''EMO'',
6,
''ECO'') REGION,
AR_CASH_RECEIPTS_ALL.AMOUNT MNT_RGL,
a.MNT_LT_RGL,
a.MNT_NLT_RGL,
a.MNT_CPTE_RGL,
a.MNT_NID_RGL
from AR_CASH_RECEIPTS_ALL,
GL_SETS_OF_BOOKS,
CC__SOCIETE,
HR_ALL_ORGANIZATION_UNITS,
AR_PAYMENT_SCHEDULES_ALL,
AR_RECEIPT_METHODS,
AR_CASH_RECEIPT_HISTORY_ALL,
HZ_CUST_ACCOUNTS,
HZ_CUST_SITE_USES_ALL,
AP_BANK_ACCOUNTS_ALL,
AP_BANK_BRANCHES,
(select ARAA.Cash_Receipt_Id, ltrim(to_char(sum(DECODE(ARAA.STATUS,
''APP'',
DECODE(ARAA.CONFIRMED_FLAG,
''N'',
0,
NVL(NVL(ARAA.AMOUNT_APPLIED_FROM,
ARAA.AMOUNT_APPLIED),
0)),
''ACTIVITY'',
DECODE(ARAA.APPLIED_PAYMENT_SCHEDULE_ID,
-2,
NVL(NVL(ARAA.AMOUNT_APPLIED_FROM,
ARAA.AMOUNT_APPLIED),
0),
-3,
NVL(NVL(ARAA.AMOUNT_APPLIED_FROM,
ARAA.AMOUNT_APPLIED),
0),
-5,
NVL(NVL(ARAA.AMOUNT_APPLIED_FROM,
ARAA.AMOUNT_APPLIED),
0),
-6,
NVL(NVL(ARAA.AMOUNT_APPLIED_FROM,
ARAA.AMOUNT_APPLIED),
0),
0),
0)),
''999999999999999999.00'')) AS MNT_LT_RGL,
ltrim(to_char(sum(DECODE(ARAA.STATUS,
''UNAPP'',
NVL(ARAA.AMOUNT_APPLIED,
0),
0)),
''999999999999999999.00'')) AS MNT_NLT_RGL,
ltrim(to_char(sum(DECODE(ARAA.STATUS,
''ACC'',
NVL(ARAA.AMOUNT_APPLIED,
0),
0)),
''999999999999999999.00'')) AS MNT_CPTE_RGL,
ltrim(to_char(sum(DECODE(ARAA.STATUS,
''UNID'',
NVL(ARAA.AMOUNT_APPLIED,
0),
0)),
''999999999999999999.00'')) AS MNT_NID_RGL from AR_RECEIVABLE_APPLICATIONS_ALL ARAA group by ARAA.Cash_Receipt_Id ) a
WHERE AR_CASH_RECEIPTS_ALL.Org_Id = GL_SETS_OF_BOOKS.attribute7
AND CPT_FUSION_PKG.F_CPT_CONV_SOCIETE(GL_SETS_OF_BOOKS.attribute6,
''SURF'') = CC__SOCIETE.cd
AND gl_sets_of_books.attribute6 = DECODE(nvl(''' || SOCIETE ||
''',''X''), ''X'', gl_sets_of_books.attribute6, nvl(''' || SOCIETE ||
''',''X'')) -- condition sur la société
AND GL_SETS_OF_BOOKS.attribute7 =
HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID
AND gl_sets_of_books.attribute5 != ''RUB_NOV''
AND a.Cash_Receipt_Id=AR_CASH_RECEIPTS_ALL.Cash_Receipt_Id
AND AR_CASH_RECEIPTS_ALL.Cash_Receipt_Id =
AR_PAYMENT_SCHEDULES_ALL.Cash_Receipt_Id
AND AR_CASH_RECEIPTS_ALL.Receipt_Method_Id =
AR_RECEIPT_METHODS.Receipt_Method_Id
AND AR_CASH_RECEIPTS_ALL.Pay_From_Customer =
HZ_CUST_ACCOUNTS.Cust_Account_Id
AND AR_CASH_RECEIPTS_ALL.Customer_Site_Use_Id =
HZ_CUST_SITE_USES_ALL.Site_Use_Id
AND AR_CASH_RECEIPTS_ALL.remittance_bank_account_id =
AP_BANK_ACCOUNTS_ALL.Bank_Account_Id
AND AP_BANK_ACCOUNTS_ALL.BANK_BRANCH_ID =
AP_BANK_BRANCHES.BANK_BRANCH_ID
AND AR_CASH_RECEIPTS_ALL.Cash_Receipt_Id =
AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_ID(+)
AND AR_CASH_RECEIPT_HISTORY_ALL.First_Posted_Record_Flag=''Y''
AND AR_CASH_RECEIPT_HISTORY_ALL.GL_DATE < ''' || DATE_GL || '''
AND AR_CASH_RECEIPTS_ALL.Status!=''REV''
AND ( to_number(a.MNT_LT_RGL,''999999999999999999.00'') = 0 -- non lettré
OR to_number(a.MNT_NLT_RGL,''999999999999999999.00'') > 0 -- partiellement lettré
OR (to_number(a.MNT_LT_RGL,''999999999999999999.00'') = AR_CASH_RECEIPTS_ALL.AMOUNT AND AR_PAYMENT_SCHEDULES_ALL.DUE_DATE > ''' ||
DATE_GL || '''))
)'; |
