Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION r_achievement_vs_target_csv(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, character varying, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetailSummary REFCURSOR := 'refDetailSummary';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pRoleId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pOuId ALIAS FOR $6;
- pSalesmanId ALIAS FOR $7;
- pCustomerId ALIAS FOR $8;
- pYear ALIAS FOR $9;
- pPeriod ALIAS FOR $10;
- pGroupBrand ALIAS FOR $11;
- vPartnerCodeMou character varying(50);
- vPartnerNameMou character varying(100);
- vDocTypeIdSOByBrand bigint;
- vStatusReleased character varying(1);
- vStatusFinal character varying(1);
- vStatusInProgress character varying(1);
- vStatusDraft character varying(1);
- vDocTypeIdReturnNote bigint;
- vDocTypeIdSalesInvoice bigint;
- vDocTypeIdInvoiceTemp bigint;
- vDocTypeIdRRS bigint;
- vStatusMapping character varying(1);
- vStatusApproved character varying(1);
- vRaw character varying(20);
- vSummary character varying(20);
- vEmptyId bigint;
- vOne integer := 1;
- vEmptyValue character varying;
- vFlagYes character varying(1) :='Y';
- vFlagNo character varying(1) :='N';
- vCurrentDate character varying;
- vSystemCurrentYear character varying;
- vLastYear character varying;
- vEndDate character varying;
- vDateTo character varying;
- vFlgTypeSOReg character varying;
- vZero numeric;
- vDocTypeIdSOB bigint;
- vComboGroupBrand character varying;
- vArraySeparator character varying;
- vQuote character varying;
- vDocTypeIdReturnInvoice bigint;
- vDocTypeIdCNAR bigint;
- vDocTypeIdDNAR bigint;
- i bigint;
- vFilterCustomer character varying;
- vDecimalNo int;
- vFilterGroupBrand character varying;
- vConfigSeparator character varying;
- vConfigPrefix character varying;
- vSalesmanLevel bigint;
- vFilterSalesman character varying;
- vSpaceValue character varying;
- vDash character varying;
- vPositionLevelManager bigint;
- BEGIN
- vPartnerCodeMou := ' ';
- vPartnerNameMou := ' ';
- vDocTypeIdSOByBrand := 398;
- vStatusReleased := 'R';
- vStatusFinal := 'F';
- vStatusInProgress := 'I';
- vStatusDraft := 'D';
- vDocTypeIdReturnNote := 502;
- vDocTypeIdSalesInvoice := 321;
- vDocTypeIdInvoiceTemp := 361;
- vDocTypeIdRRS :=381;
- vStatusMapping := 'M';
- vStatusApproved := 'A';
- vRaw := 'RAW';
- vSummary := 'SUMMARY';
- vEmptyId := -99;
- vEmptyValue := '';
- vEndDate := '1231';
- vFlgTypeSOReg := 'REG';
- vZero := 0;
- vDocTypeIdSOB := 398;
- vComboGroupBrand := 'GROUPBRANDPARENT';
- vArraySeparator := ', ';
- vQuote := '''';
- vDocTypeIdReturnInvoice := 380;
- vDocTypeIdCNAR := 251;
- vDocTypeIdDNAR := 241;
- i := 5; --Level salesman senior
- vFilterCustomer := '';
- vDecimalNo := 2;
- vFilterGroupBrand := '';
- vConfigSeparator := ';';
- vConfigPrefix := 'ACTIVITY_GL_GROUP_BRAND_';
- vSalesmanLevel := -99;
- vFilterSalesman := '';
- vSpaceValue := ' ';
- vDash := '-';
- vPositionLevelManager := 3;
- SELECT TO_CHAR(current_timestamp, 'YYYY') INTO vSystemCurrentYear;
- SELECT TO_CHAR(TO_DATE(pYear,'YYYY') - INTERVAL '1 Year','YYYY') INTO vLastYear;
- IF (pYear >= vSystemCurrentYear) THEN
- SELECT TO_CHAR(current_timestamp, 'YYYYMMDD') INTO vDateTo;
- ELSIF (pYear < vSystemCurrentYear) THEN
- SELECT pYear || vEndDate INTO vDateTo;
- END IF;
- SELECT f_get_salesman_level(pSalesmanId) INTO vSalesmanLevel;
- IF(vSalesmanLevel = 1) THEN
- vFilterSalesman := ' AND A.bod_id = '||pSalesmanId||' ';
- ELSIF (vSalesmanLevel = 2) THEN
- vFilterSalesman := ' AND A.saso_id = '||pSalesmanId||' ';
- ELSIF (vSalesmanLevel = 3) THEN
- vFilterSalesman := ' AND A.sales_manager_id = '||pSalesmanId||' ';
- ELSIF (vSalesmanLevel = 4) THEN
- vFilterSalesman := ' AND A.sales_supervisor_id = '||pSalesmanId||' ';
- ELSIF (vSalesmanLevel = 5) THEN
- vFilterSalesman := ' AND A.salesman_id = '||pSalesmanId||' ';
- ELSIF (vSalesmanLevel = 6) THEN
- vFilterSalesman := ' AND A.salesman_junior_id = '||pSalesmanId||' ';
- END IF;
- IF (pCustomerId <> vEmptyId) THEN
- vFilterCustomer := ' AND A.customer_id = '|| pCustomerId ||' ';
- END IF;
- IF (pGroupBrand <> vEmptyValue) THEN
- vFilterGroupBrand := ' AND A.group_brand = '''|| pGroupBrand ||''' ';
- END IF;
- DELETE FROM tt_salesman_customer_info_for_report_achievement_and_target WHERE session_id = pSessionId;
- DELETE FROM tt_achievement_transaction_for_report_achievement_and_target WHERE session_id = pSessionId;
- DELETE FROM tt_summary_for_report_achievement_and_target WHERE session_id = pSessionId;
- EXECUTE
- 'WITH tt_salesman_customer AS(
- SELECT A.tenant_id, A.customer_id, B.partner_code AS customer_code,
- B.partner_name AS customer_name, G.city AS customer_city,
- A.salesman_junior_id, A.salesman_id, A.sales_supervisor_id,
- A.sales_manager_id, A.saso_id, A.bod_id,
- CASE WHEN (salesman_junior_id <> $4) THEN
- salesman_junior_id
- WHEN (salesman_id <> $4) THEN
- salesman_id
- WHEN (sales_supervisor_id <> $4) THEN
- sales_supervisor_id
- WHEN (sales_manager_id <> $4) THEN
- sales_manager_id
- WHEN (saso_id <> $4) THEN
- saso_id
- ELSE
- bod_id
- END AS salesman_handle_id, A.group_brand,
- $6, $6, $6, $6, $11
- FROM m_mapping_salesman_hierarchy_customer A
- INNER JOIN m_partner B ON A.customer_id = B.partner_id
- INNER JOIN m_ctgr_partner C ON A.tenant_id = C.tenant_id AND B.ctgr_partner_id = C.ctgr_partner_id
- INNER JOIN t_user_role D ON D.user_id = $7 AND D.role_id = $8
- INNER JOIN m_policy_partner E ON B.ctgr_partner_id = E.ctgr_partner_id AND D.user_role_id = E.user_role_id
- INNER JOIN m_partner_address G ON A.tenant_id = G.tenant_id AND A.customer_id = G.partner_id AND G.flg_default = $9
- WHERE A.tenant_id = 10
- AND $3 BETWEEN A.date_from AND A.date_to
- AND $12 <> $4 '
- || vFilterSalesman
- || vFilterCustomer
- || vFilterGroupBrand ||
- ')
- INSERT INTO tt_salesman_customer_info_for_report_achievement_and_target(
- session_id, tenant_id, customer_id, customer_code, customer_name,
- customer_city, salesman_junior_id, salesman_id, sales_supervisor_id,
- sales_manager_id, saso_id, bod_id, salesman_handle_id, salesman_handle_code,
- salesman_handle_name, salesman_handle_level, group_brand, curr_code,
- target_sales, q1_amount, q2_amount, q3_amount, flg_target
- )
- SELECT $1, A.tenant_id, A.customer_id, A.customer_code, A.customer_name,
- A.customer_city, A.salesman_junior_id, A.salesman_id, A.sales_supervisor_id,
- A.sales_manager_id, A.saso_id, A.bod_id, A.salesman_handle_id, f_get_partner_code(A.salesman_handle_id) AS salesman_handle_code,
- f_get_partner_name(A.salesman_handle_id) AS salesman_handle_name, f_get_salesman_level(A.salesman_handle_id) AS salesman_handle_level, A.group_brand, $4,
- $6, $6, $6, $6, $11
- FROM tt_salesman_customer A
- WHERE EXISTS (
- SELECT 1
- FROM m_partner_ou D
- INNER JOIN m_ou_structure E
- ON ( E.ou_bu_id = D.ou_id
- OR E.ou_branch_id = D.ou_id
- OR E.ou_sub_bu_id = D.ou_id )
- WHERE A.salesman_handle_id = D.partner_id
- AND e.ou_id = $10
- ) AND f_get_salesman_level(A.salesman_handle_id) >= $13'
- USING pSessionId, pTenantId, pPeriod, vEmptyId, vEmptyValue, vZero, pUserId, pRoleId, vFlagYes, pOuId, vFlagNo, vSalesmanLevel, vPositionLevelManager;
- UPDATE tt_salesman_customer_info_for_report_achievement_and_target A
- SET curr_code = B.curr_code,
- target_sales = B.target_sales,
- q1_amount = B.q1_amount,
- q2_amount = B.q2_amount,
- q3_amount = B.q3_amount,
- flg_target = vFlagYes
- FROM m_customer_target_sales B
- WHERE B.tenant_id = A.tenant_id
- AND A.customer_id = B.customer_id
- AND B.year = pYear
- AND B.group_brand = A.group_brand;
- --Temporary Sales Invoice
- EXECUTE
- 'INSERT INTO tt_achievement_transaction_for_report_achievement_and_target (
- session_id, tenant_id, ou_id,
- doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
- status_doc, workflow_status,
- group_brand_parent_code
- )
- SELECT DISTINCT ON (D.so_id) $1, A.tenant_id, C.ou_id,
- C.invoice_temp_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, B.curr_code, B.amount,
- C.status_doc AS status_doc_invoice_temp, C.workflow_status,
- G.code
- FROM tt_salesman_customer_info_for_report_achievement_and_target A
- INNER JOIN fi_invoice_ar_balance B ON B.partner_id = A.customer_id
- INNER JOIN sl_invoice_temp C ON C.invoice_temp_id = B.invoice_ar_id
- INNER JOIN sl_so_info D ON D.so_id = C.ref_id
- INNER JOIN sl_so_brand_item E ON E.so_id = D.so_id
- INNER JOIN m_brand_ext F ON F.brand_id = E.brand_id
- INNER JOIN t_combo_value G ON A.group_brand = G.code
- WHERE A.session_id = $1 AND
- C.tenant_id = $2 AND
- C.ou_id = $3 AND
- B.doc_type_id = $4 AND
- C.ref_doc_type_id = $5 AND
- D.flg_type_so = $6 AND
- SUBSTR(C.doc_date, 1, 4) BETWEEN $7 AND $8 AND
- G.combo_id = $9 AND
- $11 || F.group_brand || $11 = ANY (string_to_array (G.prop_key, $10))'
- USING pSessionId, pTenantId, pOuId, vDocTypeIdInvoiceTemp, vDocTypeIdSOB, vFlgTypeSOReg, vLastYear, pYear, vComboGroupBrand, vArraySeparator, vQuote;
- --Sales Invoice
- EXECUTE
- 'INSERT INTO tt_achievement_transaction_for_report_achievement_and_target (
- session_id, tenant_id, ou_id,
- doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
- status_doc, workflow_status,
- group_brand_parent_code
- )
- SELECT DISTINCT ON (D.so_id) $1, A.tenant_id, C.ou_id,
- C.invoice_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, B.curr_code, B.amount,
- C.status_doc AS status_doc_invoice_temp, C.workflow_status,
- G.code
- FROM tt_salesman_customer_info_for_report_achievement_and_target A
- INNER JOIN fi_invoice_ar_balance B ON B.partner_id = A.customer_id
- INNER JOIN sl_invoice C ON C.invoice_id = B.invoice_ar_id
- INNER JOIN sl_so_info D ON D.so_id = C.ref_id
- INNER JOIN sl_so_brand_item E ON E.so_id = D.so_id
- INNER JOIN m_brand_ext F ON F.brand_id = E.brand_id
- INNER JOIN t_combo_value G ON A.group_brand = G.code
- WHERE A.session_id = $1 AND
- C.tenant_id = $2 AND
- C.ou_id = $3 AND
- B.doc_type_id = $4 AND
- C.ref_doc_type_id = $5 AND
- D.flg_type_so = $6 AND
- SUBSTR(C.doc_date, 1, 4) BETWEEN $7 AND $8 AND
- G.combo_id = $9 AND
- $11 || F.group_brand || $11 = ANY (string_to_array (G.prop_key, $10))'
- USING pSessionId, pTenantId, pOuId, vDocTypeIdSalesInvoice, vDocTypeIdSOB, vFlgTypeSOReg, vLastYear, pYear, vComboGroupBrand, vArraySeparator, vQuote;
- --Return
- EXECUTE
- 'INSERT INTO tt_achievement_transaction_for_report_achievement_and_target (
- session_id, tenant_id, ou_id,
- doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
- status_doc, workflow_status,
- group_brand_parent_code
- )
- SELECT DISTINCT ON (D.request_return_sales_id) $1, A.tenant_id, C.ou_id,
- C.invoice_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, B.curr_code, B.amount,
- C.status_doc AS status_doc_invoice_temp, C.workflow_status,
- G.code
- FROM tt_salesman_customer_info_for_report_achievement_and_target A
- INNER JOIN fi_invoice_ar_balance B ON B.partner_id = A.customer_id
- INNER JOIN sl_invoice C ON C.invoice_id = B.invoice_ar_id
- INNER JOIN sl_request_return_sales_brand_item D ON D.request_return_sales_id = C.ref_id
- INNER JOIN m_brand_ext F ON F.brand_id = D.brand_id
- INNER JOIN t_combo_value G ON A.group_brand = G.code
- WHERE A.session_id = $1 AND
- C.tenant_id = $2 AND
- C.ou_id = $3 AND
- B.doc_type_id = $4 AND
- C.ref_doc_type_id = $5 AND
- SUBSTR(C.doc_date, 1, 4) BETWEEN $7 AND $8 AND
- G.combo_id = $9 AND
- $11 || F.group_brand || $11 = ANY (string_to_array (G.prop_key, $10))'
- USING pSessionId, pTenantId, pOuId, vDocTypeIdReturnInvoice, vDocTypeIdRRS, vFlgTypeSOReg, vLastYear, pYear, vComboGroupBrand, vArraySeparator, vQuote;
- --CN AR
- EXECUTE
- 'INSERT INTO tt_achievement_transaction_for_report_achievement_and_target (
- session_id, tenant_id, ou_id,
- doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
- status_doc, workflow_status,
- group_brand_parent_code
- )
- SELECT $1, A.tenant_id, C.ou_id,
- C.invoice_ar_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, B.curr_code, B.amount,
- C.status_doc AS status_doc_invoice_temp, C.workflow_status,
- G.code
- FROM tt_salesman_customer_info_for_report_achievement_and_target A
- INNER JOIN fi_invoice_ar_balance B ON B.partner_id = A.customer_id
- INNER JOIN fi_invoice_ar C ON C.invoice_ar_id = B.invoice_ar_id
- INNER JOIN t_combo_value G ON A.group_brand = G.code
- WHERE A.session_id = $1 AND
- C.tenant_id = $2 AND
- C.ou_id = $3 AND
- B.doc_type_id = $4 AND
- SUBSTR(C.doc_date, 1, 4) BETWEEN $7 AND $8 AND
- G.combo_id = $9 AND
- EXISTS(
- SELECT 1
- FROM fi_invoice_ar_cost D
- WHERE D.invoice_ar_id = C.invoice_ar_id AND
- f_get_activity_gl_code(D.activity_gl_id) = ANY (string_to_array (f_get_value_system_config_by_param_code($2, $13 || G.code), $12))
- )'
- USING pSessionId, pTenantId, pOuId, vDocTypeIdCNAR, vDocTypeIdCNAR, vFlgTypeSOReg, vLastYear, pYear, vComboGroupBrand, vArraySeparator, vQuote, vConfigSeparator, vConfigPrefix;
- --DN AR
- EXECUTE
- 'INSERT INTO tt_achievement_transaction_for_report_achievement_and_target (
- session_id, tenant_id, ou_id,
- doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
- status_doc, workflow_status,
- group_brand_parent_code
- )
- SELECT $1, A.tenant_id, C.ou_id,
- C.invoice_ar_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, B.curr_code, B.amount,
- C.status_doc AS status_doc_invoice_temp, C.workflow_status,
- G.code
- FROM tt_salesman_customer_info_for_report_achievement_and_target A
- INNER JOIN fi_invoice_ar_balance B ON B.partner_id = A.customer_id
- INNER JOIN fi_invoice_ar C ON C.invoice_ar_id = B.invoice_ar_id
- INNER JOIN t_combo_value G ON A.group_brand = G.code
- WHERE A.session_id = $1 AND
- C.tenant_id = $2 AND
- C.ou_id = $3 AND
- B.doc_type_id = $4 AND
- SUBSTR(C.doc_date, 1, 4) BETWEEN $7 AND $8 AND
- G.combo_id = $9 AND
- EXISTS(
- SELECT 1
- FROM fi_invoice_ar_cost D
- WHERE D.invoice_ar_id = C.invoice_ar_id AND
- f_get_activity_gl_code(D.activity_gl_id) = ANY (string_to_array (f_get_value_system_config_by_param_code($2, $13 || G.code), $12))
- )'
- USING pSessionId, pTenantId, pOuId, vDocTypeIdDNAR, vDocTypeIdDNAR, vFlgTypeSOReg, vLastYear, pYear, vComboGroupBrand, vArraySeparator, vQuote, vConfigSeparator, vConfigPrefix;
- WITH tt_grouped_summary AS (
- SELECT A.tenant_id, A.salesman_handle_id AS salesman_id,
- A.target_sales AS target,
- SUM(COALESCE(B.amount, 0)) AS year_to_date_accumulative_amount,
- A.salesman_handle_level AS salesman_level,
- A.group_brand AS group_brand_parent_code
- FROM tt_salesman_customer_info_for_report_achievement_and_target A
- LEFT JOIN tt_achievement_transaction_for_report_achievement_and_target B
- ON B.partner_id = A.customer_id AND
- B.tenant_id = A.tenant_id AND
- SUBSTR(B.doc_date, 1, 4) = pYear AND
- B.group_brand_parent_code = A.group_brand
- WHERE A.session_id = pSessionId AND
- A.tenant_id = pTenantId AND
- A.flg_target = vFlagYes
- GROUP BY A.tenant_id, A.salesman_handle_id, A.target_sales, A.salesman_handle_level, A.group_brand, B.partner_id
- )
- INSERT INTO tt_summary_for_report_achievement_and_target (
- session_id, tenant_id,
- salesman_id,
- original_target_amount,
- target_amount,
- original_year_to_date_accumulative_amount,
- year_to_date_accumulative_amount,
- salesman_level, group_brand_parent_code,
- salesman_handle_id, salesman_handle_level
- )
- SELECT pSessionId, A.tenant_id,
- A.salesman_id,
- SUM(A.target) AS target,
- SUM(A.target) AS target,
- SUM(A.year_to_date_accumulative_amount) AS year_to_date_accumulative_amount,
- SUM(A.year_to_date_accumulative_amount) AS year_to_date_accumulative_amount,
- A.salesman_level,
- A.group_brand_parent_code,
- A.salesman_id, A.salesman_level
- FROM tt_grouped_summary A
- GROUP BY A.tenant_id, A.salesman_id, A.salesman_level, A.group_brand_parent_code;
- --Insert atasan dari salesman_handle yang belum ada pada table
- PERFORM f_insert_leader_summary_for_report_achievment_vs_target(pSessionId, pTenantId, A.salesman_id, A.group_brand_parent_code)
- FROM tt_summary_for_report_achievement_and_target A
- WHERE A.session_id = pSessionId AND
- A.tenant_id = pTenantId;
- WHILE i >= vPositionLevelManager LOOP
- PERFORM f_get_total_achievement_for_report_achievement_vs_target(pSessionId, pTenantId, A.salesman_id, A.salesman_level, A.group_brand_parent_code, A.salesman_handle_id, A.salesman_handle_level)
- FROM tt_summary_for_report_achievement_and_target A
- WHERE A.session_id = pSessionId AND
- A.tenant_id = pTenantId AND
- A.salesman_level = i;
- i := i - 1 ;
- END LOOP ;
- Open pRefHeader FOR
- SELECT 21 AS _COUNT,
- 'Nama', 'Target', 'YTD Acv', '% YTD Acv', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
- ';' AS _separator;
- RETURN NEXT pRefHeader;
- PERFORM SETVAL('r_achievement_vs_target_line_no_seq', 0);
- Open pRefDetail FOR
- WITH tt_ordered_achievement AS (
- SELECT B.customer_code, B.customer_name, B.customer_city,
- CASE WHEN f_get_partner_name(B.sales_manager_id) <> vSpaceValue THEN f_get_partner_name(B.sales_manager_id) ELSE vDash END AS manager_name,
- CASE WHEN f_get_partner_name(B.sales_supervisor_id) <> vSpaceValue THEN f_get_partner_name(B.sales_supervisor_id) ELSE vDash END AS supervisor_name,
- CASE WHEN f_get_partner_name(B.salesman_id) <> vSpaceValue THEN f_get_partner_name(B.salesman_id) ELSE vDash END AS senior_sales_name,
- CASE WHEN f_get_partner_name(B.salesman_junior_id) <> vSpaceValue THEN f_get_partner_name(B.salesman_junior_id) ELSE vDash END AS junior_sales_name,
- ROUND(SUM(
- CASE WHEN C.year_date = vLastYear THEN
- COALESCE(A.amount, 0)
- ELSE
- 0
- END
- ), vDecimalNo) AS last_year_achievement_amount,
- B.target_sales AS current_year_target_amount, B.q1_amount, B.q2_amount, B.q3_amount,
- ROUND(SUM(
- CASE WHEN C.year_date = pYear THEN
- COALESCE(A.amount, 0)
- ELSE
- 0
- END
- ), vDecimalNo) AS year_to_date_amount,
- ROUND(SUM(
- CASE WHEN C.q_no = 1 AND C.year_date = pYear THEN
- COALESCE(A.amount, 0)
- ELSE
- 0
- END
- ), vDecimalNo) AS q1_achievement_amount,
- ROUND(SUM(
- CASE WHEN C.q_no = 2 AND C.year_date = pYear THEN
- COALESCE(A.amount, 0)
- ELSE
- 0
- END
- ), vDecimalNo) AS q2_achievement_amount,
- ROUND(SUM(
- CASE WHEN C.q_no = 3 AND C.year_date = pYear THEN
- COALESCE(A.amount, 0)
- ELSE
- 0
- END
- ), vDecimalNo) AS q3_achievement_amount
- FROM tt_salesman_customer_info_for_report_achievement_and_target B
- LEFT JOIN tt_achievement_transaction_for_report_achievement_and_target A
- ON A.partner_id = B.customer_id AND
- A.tenant_id = B.tenant_id AND
- A.group_brand_parent_code = B.group_brand AND
- A.partner_id = B.customer_id AND
- A.session_id = B.session_id
- LEFT JOIN dt_date C ON A.doc_date = C.string_date
- WHERE B.session_id = pSessionId
- AND (B.customer_code <> vEmptyValue OR B.customer_name <> vEmptyValue)
- AND B.flg_target = vFlagYes
- GROUP BY B.customer_code, B.customer_name, B.customer_city,
- B.sales_manager_id, B.sales_supervisor_id, B.salesman_id, B.salesman_junior_id,
- B.target_sales, B.q1_amount, B.q2_amount, B.q3_amount, group_brand_parent_code
- ORDER BY manager_name, supervisor_name, senior_sales_name, customer_city, year_to_date_amount DESC
- ), data_detail AS (
- SELECT f_get_partner_name(A.salesman_id) AS no, ROUND(SUM(A.target_amount), vDecimalNo)::character varying AS code,
- ROUND(SUM(A.year_to_date_accumulative_amount), vDecimalNo)::character varying AS cust_name,
- (CASE WHEN SUM(A.target_amount) > 0 THEN ROUND(SUM(A.year_to_date_accumulative_amount) * 100 / SUM(A.target_amount), vDecimalNo) ELSE 100 END)::character varying AS kota,
- vEmptyValue AS manager, vEmptyValue AS spv, vEmptyValue AS senior_sales, vEmptyValue AS junior_sales,
- vEmptyValue AS last_year_achv, vEmptyValue AS target_current_year, vEmptyValue AS target_q1,
- vEmptyValue AS target_q2, vEmptyValue AS target_q3, vEmptyValue AS achievment_amount_ytd,
- vEmptyValue AS achievment_ytd_percentage, vEmptyValue AS achievment_amount_q1,
- vEmptyValue AS achievment_q1_percentage, vEmptyValue AS achievment_amount_q2,
- vEmptyValue AS achievment_q2_percentage, vEmptyValue AS achievment_amount_q3,
- vEmptyValue AS achievment_q3_percentage, 1 AS ordial
- FROM tt_summary_for_report_achievement_and_target A
- WHERE A.session_id = pSessionId AND
- A.tenant_id = pTenantId AND
- A.salesman_id <> vEmptyId
- GROUP BY A.tenant_id, A.salesman_id, A.salesman_level
- UNION ALL
- SELECT vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,
- vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue, 2 AS ordial
- UNION ALL
- SELECT 'No', 'Code', 'Cust. Name', 'Kota',
- 'Manager', 'SPV', 'Senior Sales', 'Junior Sales',
- 'Last Year Achv ('||vLastYear||')', 'Target '||pYear,
- 'Target Q1 '||pYear, 'Target Q2 '||pYear, 'Target Q3 '||pYear,
- 'ACHIEVEMENT Amount YTD', 'ACHIEVEMENT YTD %',
- 'ACHIEVEMENT Amount Q1', 'ACHIEVEMENT Q1 %', 'ACHIEVEMENT Amount Q2', 'ACHIEVEMENT Q2 %', 'ACHIEVEMENT Amount Q3', 'ACHIEVEMENT Q3 %', 3 AS ordial
- UNION ALL
- SELECT (row_number() OVER ())::character varying AS no, A.customer_code, A.customer_name, A.customer_city,
- A.manager_name, A.supervisor_name, A.senior_sales_name, A.junior_sales_name,
- A.last_year_achievement_amount::character varying, A.current_year_target_amount::character varying,
- ROUND(A.q1_amount, vDecimalNo)::character varying, ROUND(A.q2_amount, vDecimalNo)::character varying, ROUND(A.q3_amount, vDecimalNo)::character varying,
- A.year_to_date_amount::character varying, (CASE WHEN A.current_year_target_amount > 0 THEN ROUND(A.year_to_date_amount * 100 / A.current_year_target_amount, vDecimalNo) ELSE 100 END)::character varying,
- A.q1_achievement_amount::character varying, (CASE WHEN A.q1_amount > 0 THEN ROUND(A.q1_achievement_amount * 100 / A.q1_amount, vDecimalNo) ELSE 100 END)::character varying,
- A.q2_achievement_amount::character varying, (CASE WHEN A.q2_amount > 0 THEN ROUND(A.q2_achievement_amount * 100 / A.q2_amount, vDecimalNo) ELSE 100 END)::character varying,
- A.q3_achievement_amount::character varying, (CASE WHEN A.q3_amount > 0 THEN ROUND(A.q3_achievement_amount * 100 / A.q3_amount, vDecimalNo) ELSE 100 END)::character varying,
- row_number() OVER () + 3 AS ordial
- FROM tt_ordered_achievement A
- ORDER BY ordial, no
- ), summary_detail AS (
- SELECT ROUND(SUM(A.last_year_achievement_amount), vDecimalNo)::character varying AS last_year_achv,
- ROUND(SUM(A.current_year_target_amount), vDecimalNo)::character varying AS target_current_year,
- ROUND(SUM(A.q1_amount), vDecimalNo)::character varying AS target_q1,
- ROUND(SUM(A.q2_amount), vDecimalNo)::character varying AS target_q2,
- ROUND(SUM(A.q3_amount), vDecimalNo)::character varying AS target_q3,
- ROUND(SUM(A.year_to_date_amount), vDecimalNo)::character varying AS achievment_amount_ytd,
- (CASE WHEN SUM(A.current_year_target_amount) > 0 THEN ROUND(SUM(A.year_to_date_amount) * 100 / SUM(A.current_year_target_amount), vDecimalNo) ELSE 100 END)::character varying AS achievment_ytd_percentage,
- ROUND(SUM(A.q1_achievement_amount), vDecimalNo)::character varying AS achievment_amount_q1,
- (CASE WHEN SUM(A.q1_amount) > 0 THEN ROUND(SUM(A.q1_achievement_amount) * 100 / SUM(A.q1_amount), vDecimalNo) ELSE 100 END)::character varying AS achievment_q1_percentage,
- ROUND(SUM(A.q2_achievement_amount), vDecimalNo)::character varying AS achievment_amount_q2,
- (CASE WHEN SUM(A.q2_amount) > 0 THEN ROUND(SUM(A.q2_achievement_amount) * 100 / SUM(A.q2_amount), vDecimalNo) ELSE 100 END)::character varying AS achievment_q2_percentage,
- ROUND(SUM(A.q3_achievement_amount), vDecimalNo)::character varying AS achievment_amount_q3,
- (CASE WHEN SUM(A.q3_amount) > 0 THEN ROUND(SUM(A.q3_achievement_amount) * 100 / SUM(A.q3_amount), vDecimalNo) ELSE 100 END)::character varying AS achievment_q3_percentage
- FROM tt_ordered_achievement A
- )
- SELECT A.no, A.code, A.cust_name, A.kota, A.manager, A.spv, A.senior_sales, A.junior_sales,
- A.last_year_achv, A.target_current_year, A.target_q1, A.target_q2, A.target_q3,
- A.achievment_amount_ytd, A.achievment_ytd_percentage, A.achievment_amount_q1,
- A.achievment_q1_percentage, A.achievment_amount_q2, A.achievment_q2_percentage,
- A.achievment_amount_q3, A.achievment_q3_percentage
- FROM data_detail A
- UNION ALL
- SELECT 'Total', vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
- COALESCE(A.last_year_achv, '0'), COALESCE(A.target_current_year, '0'), COALESCE(A.target_q1, '0'), COALESCE(A.target_q2, '0'), COALESCE(A.target_q3, '0'),
- COALESCE(A.achievment_amount_ytd, '0'),
- CASE WHEN (A.target_current_year IS NULL AND A.achievment_amount_ytd IS NULL) THEN '0' ELSE A.achievment_ytd_percentage END AS achievment_ytd_percentage,
- COALESCE(A.achievment_amount_q1, '0'),
- CASE WHEN (A.target_q1 IS NULL AND A.achievment_amount_q1 IS NULL) THEN '0' ELSE A.achievment_q1_percentage END AS achievment_q1_percentage,
- COALESCE(A.achievment_amount_q2, '0'),
- CASE WHEN (A.target_q2 IS NULL AND A.achievment_amount_q2 IS NULL) THEN '0' ELSE A.achievment_q2_percentage END AS achievment_q2_percentage,
- COALESCE(A.achievment_amount_q3, '0'),
- CASE WHEN (A.target_q3 IS NULL AND A.achievment_amount_q3 IS NULL) THEN '0' ELSE A.achievment_q3_percentage END AS achievment_q3_percentage
- FROM summary_detail A;
- RETURN NEXT pRefDetail;
- DELETE FROM tt_salesman_customer_info_for_report_achievement_and_target WHERE session_id = pSessionId;
- DELETE FROM tt_achievement_transaction_for_report_achievement_and_target WHERE session_id = pSessionId;
- DELETE FROM tt_summary_for_report_achievement_and_target WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement