You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
33 lines
3.5 KiB
Plaintext
33 lines
3.5 KiB
Plaintext
SELECT C.COMPANY||SUBSTR( '&[-C--L]Company',1,0) "COMPANY",
|
|
C.PROJECT_GROUP "PROJECT GROUP" ,
|
|
B.PROJECT_ID "PROJECT ID" ,
|
|
ifsapp.ACCOUNT_API.Get_Logical_Account_Type_Db( b.company, b.account) accnt_type,
|
|
B.ACCOUNT "ACCOUNT" ,
|
|
C.PROJECT_STATUS ,
|
|
to_char(MAX( C.READY_DATE) ,'YYYY-MM-DD') "COMPLETION DATE",
|
|
D.PROJECT_TYPE "PROJECT TYPE" ,
|
|
C.PROJECT_LEADER "PROJECT LEADER" ,
|
|
SUM( amount_balance) "total_amount" ,
|
|
SUM( DECODE( SIGN( TO_NUMBER(B.ACCOUNTING_YEAR||DECODE(SIGN(10-B.ACCOUNTING_PERIOD),1,'0'||B.ACCOUNTING_PERIOD,B.ACCOUNTING_PERIOD)) - TO_NUMBER('&[M-S--]Until_year'||DECODE(SIGN(10-'&[M-S--]From_period'),1,'0'|| '&[M-S--]From_period','&[M-S--]From_period'))),-1,0,1,b.amount_balance,0,b.amount_balance)) + DECODE('&[M-S--]until_period',1,0,0) amount_balance,
|
|
|
|
SUM( DECODE( SIGN( TO_NUMBER(B.ACCOUNTING_YEAR||DECODE(SIGN(10-B.ACCOUNTING_PERIOD),1,'0'||B.ACCOUNTING_PERIOD,B.ACCOUNTING_PERIOD)) - TO_NUMBER('&[M-S--]Until_year'||'01')),-1,0,1,b.amount_balance,0,b.amount_balance)) until_amount_balance,
|
|
SUM( DECODE(B.BUDGET_VERSION, '&Budget_version',B.BUDGET_AMOUNT,0)) total_budget_amount,
|
|
SUM( DECODE( SIGN( TO_NUMBER(B.ACCOUNTING_YEAR||DECODE(SIGN(10-B.ACCOUNTING_PERIOD),1,'0'||B.ACCOUNTING_PERIOD,B.ACCOUNTING_PERIOD)) - TO_NUMBER( '&[M-S--]Until_year'||DECODE(SIGN(10-'&[M-S--]From_period'),1,'0'||'&[M-S--]From_period','&[M-S--]From_period'))),-1,0,1,DECODE(B.BUDGET_VERSION, '&Budget_version',B.BUDGET_AMOUNT, 0),0,DECODE(B.BUDGET_VERSION, '&Budget_version',B.BUDGET_AMOUNT, 0))) budget_amount,
|
|
SUM( DECODE( SIGN( TO_NUMBER(B.ACCOUNTING_YEAR||DECODE(SIGN(10-B.ACCOUNTING_PERIOD),1,'0'||B.ACCOUNTING_PERIOD,B.ACCOUNTING_PERIOD)) - TO_NUMBER('&[M-S--]Until_year'||'01')),-1,0,0,DECODE(B.BUDGET_VERSION, '&Budget_version', B.BUDGET_AMOUNT),1,DECODE(B.BUDGET_VERSION, '&Budget_version',B.BUDGET_AMOUNT))) until_budget_amount
|
|
FROM ifsapp.PROJECT_BALANCE_BUDGET B
|
|
inner join ifsapp.ACCOUNTING_PROJECT C on B.PROJECT_ID= C.PROJECT_ID and B.COMPANY = C.COMPANY
|
|
inner join ifsapp.ACCOUNTING_PROJECT_LEDGER D on C.PROJECT_ID= D.PROJECT_ID and C.COMPANY = D.COMPANY
|
|
|
|
WHERE
|
|
( C.COMPANY LIKE NVL( '&[-C--L]Company' ,'%') OR C.Company in (SELECT REGEXP_SUBSTR(d__.str, '[^;]+', 1, LEVEL) AS val_ FROM (SELECT '&[-C--L]Company' AS str FROM DUAL) d__ CONNECT BY LEVEL <= REGEXP_COUNT(d__.str, ';') + 1) )
|
|
AND D.ledger_id IN ('*', '00')
|
|
AND B.ACTIVATE_CODE = 'COST/REV'
|
|
AND D.project_type != 'P' AND Account_API.Get_Logical_Account_Type_Db(b.company , b.account) IN ('R','C')
|
|
AND (TO_NUMBER(B.ACCOUNTING_YEAR||DECODE(SIGN(10-B.ACCOUNTING_PERIOD),1,'0'||B.ACCOUNTING_PERIOD,B.ACCOUNTING_PERIOD)) <= TO_NUMBER('&[M-S--]Until_year'||DECODE(SIGN(10-'&[M-S--]until_period'),1,'0'||'&[M-S--]until_period','&[M-S--]until_period')))
|
|
AND ('&Project_status' = '%' OR C.PROJECT_STATUS LIKE NVL( '&Project_status','%' ) OR C.PROJECT_STATUS IS NULL)
|
|
|
|
|
|
GROUP BY C.COMPANY,C.PROJECT_GROUP, B.PROJECT_ID,
|
|
ifsapp.ACCOUNT_API.Get_Logical_Account_Type_Db( b.company, b.account),
|
|
B.ACCOUNT, D.PROJECT_TYPE, C.PROJECT_LEADER, C.PROJECT_STATUS
|
|
ORDER BY C.COMPANY,C.PROJECT_GROUP, B.PROJECT_ID, accnt_type DESC, B.ACCOUNT ASC |